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:-

FIFO 1

FIFO 2

FIFO 3

FIFO 4

FIFO 5

FIFO 6

FIFO 7

FIFO 8

FIFO 9

FIFO 10

FIFO 11

FIFO 12

FIFO 13

Part 1

CTRM53002A1

LEATHER L538 TOPPER ESPRESSO

Part 2

CTRM53003A1

LEATHER L538 TOPPER DARK CHERRY

A4

A3

A5

Part 3

CTRM53004A1

LEATHER L538 TOPPER LUNAR

Part 4

CTRM53119A1

LEATHER PRE SHRUNK EBONY

A2

<tbody>
</tbody>