Macros delete values & shift left keeping dropdown list inplace

[dnorm]

HI All

I am trying to setup a FIFO (first in first out) sheet for stock. Ideally I would like to have 'CTRL+G' as the short cut to run the macro (currently set to a button).
the issue I have is keeping the dropdown list within the ranged cells. my current macro deletes everything, so formulae and dropdowns are eradicated as well.

What I am after if possible is either an auto update, or CRTL+G of macro to condense the FIFO table if the cell is empty, but keep the dropdown list (my current macro is a simple delete shift left one).


Any help would be gratefully received

This is my current macro:-

Sub Makro1()
'
Dim rng As Range
With ActiveSheet
With Intersect(.UsedRange, .Range("D6:W1000"))
.Value = .Value
For Each cll In .Cells
If Len(cll.Value) = 0 Then Set rng = Union(cll, IIf(rng Is Nothing, cll, rng))
Next cll
End With
End With
rng.Delete xlShiftToLeft
End Sub


Here is a sample of the destination sheet:-[TABLE="width: 1058"]
<tbody>[TR]
[TD="width: 111, bgcolor: transparent"][/TD]
[TD="width: 225, bgcolor: transparent"][/TD]
[TD="width: 243, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]FIFO 1
[/TD]
[TD="width: 64, bgcolor: transparent"]FIFO 2
[/TD]
[TD="width: 64, bgcolor: transparent"]FIFO 3
[/TD]
[TD="width: 64, bgcolor: transparent"]FIFO 4
[/TD]
[TD="width: 64, bgcolor: transparent"]FIFO 5
[/TD]
[TD="width: 64, bgcolor: transparent"]FIFO 6
[/TD]
[TD="width: 64, bgcolor: transparent"]FIFO 7
[/TD]
[TD="width: 64, bgcolor: transparent"]FIFO 8
[/TD]
[TD="width: 64, bgcolor: transparent"]FIFO 9
[/TD]
[TD="width: 64, bgcolor: transparent"]FIFO 10
[/TD]
[TD="width: 64, bgcolor: transparent"]FIFO 11
[/TD]
[TD="width: 64, bgcolor: transparent"]FIFO 12
[/TD]
[TD="width: 64, bgcolor: transparent"]FIFO 13
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Part 1
[/TD]
[TD="class: xl64, bgcolor: transparent"]CTRM53002A1
[/TD]
[TD="class: xl64, bgcolor: transparent"]LEATHER L538 TOPPER ESPRESSO
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Part 2
[/TD]
[TD="class: xl64, bgcolor: transparent"]CTRM53003A1
[/TD]
[TD="class: xl64, bgcolor: transparent"]LEATHER L538 TOPPER DARK CHERRY
[/TD]
[TD="class: xl64, bgcolor: transparent"]A4
[/TD]
[TD="class: xl64, bgcolor: transparent"]A3
[/TD]
[TD="class: xl64, bgcolor: transparent"]A5
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Part 3
[/TD]
[TD="class: xl64, bgcolor: transparent"]CTRM53004A1
[/TD]
[TD="class: xl64, bgcolor: transparent"]LEATHER L538 TOPPER LUNAR
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]Part 4
[/TD]
[TD="class: xl64, bgcolor: transparent"]CTRM53119A1
[/TD]
[TD="class: xl64, bgcolor: transparent"]LEATHER PRE SHRUNK EBONY
[/TD]
[TD="class: xl64, bgcolor: transparent"]A2
[/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[TD="class: xl64, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]