Need a macro that will shift data to empty cells within a range.

[olismith89]

I have searched for hours and can't find what I'm looking for so hope someone can help me.

I have a table of data (A25:G55) to indicate outstanding orders of a product. They are in order of when each order was placed, so not in any specific numerical order. What I want to be able to do is delete all the orders I have completed and then have a macro button that I can click which shifts all the data (but keeps it in the same order as inputted) up into the top left box (B25). Example below.

	A	B	C	D	E	F	G
25	X	15	80	75	90	5	10
26	75	90	70	25	20	10	5

If I completed orders 15, 80 and 75, I want to be able to manually delete the cells containing those numbers, leaving 3 blank cells, and then click a button to shift it to the example below.

	A	B	C	D	E	F	G
25	X	90	5	10	75	90	70
26	25	20	10	5

The "X" is A25 and is essentially a 'dead cell' that is not in use.

Hope someone can help. Happy to clarify further info if needed.

 

[Zot]

Try this

Sub Rearrange()

Dim cell As Range, rngData As Range
Dim i As Long, j As Long, n As Long
Dim AryData(0 To 217) As Variant, e As Variant

Set rngData = Range("A25", "G55")

n = 0
For i = 25 To 55
    For j = 1 To 7
        If Not Cells(i, j) = 0 Then
            AryData(n) = Cells(i, j)
            n = n + 1
        End If
    Next
Next

n = 0
For Each cell In rngData
    cell = AryData(n)
    n = n + 1
Next

End Sub

 

[olismith89]

Hi there, when trying this, I get the following error message.

Cannot run the macro ''<filename'!macroname'>. The macro may not be available in this workbook or all macros may be disabled.
I know macro's aren't disabled as all other macros work.

 

[olismith89]

Apologies, the code above is correct, I just made an error assigning the macro.

 

[Zot]

Apologies, the code above is correct, I just made an error assigning the macro.

Thanks for update. I am in different time zone to give prompt reply obviously ?