Move non-blank values in a range to the top rows (VBA)

[Grummet]

Hello all,

What I have is a small range with 5 rows and 2 columns. What I would like to do is turn this:

Deposit A	100.00
x	x
x	x
Deposit B	200.00
Deposit C	300.00

<tbody>
</tbody>

Into this:

Deposit A	100.00
Deposit B	200.00
Deposit C	300.00
x	x
x	x

<tbody>
</tbody>

However, I do not want to actually MOVE any cells by cutting/pasting or sorting. I only want the values to be shifted around.

My first thought would be to go through each row in the range and determine if it's blank, and if so then copy the values up from the row below, then clear that copied row of it's data and continue the check.

However, if I write it to run through each row in the range one time then I imagine it would come up like this:

Deposit A	100.00
x	x
Deposit B	200.00
Deposit C	300.00
x	x

<tbody>
</tbody>

I suppose I could have it run through the check several times to ensure that all of the data has been moved up, but I know there has to be a better way.

What I would like to write, but don't know how, would be something like:

Step 1: Evaluate the rows in the given range.
Step 2: "Save" any row that has at least one non-blank value in it. Ignore all the completely blank rows.
Step 3: Probably also count the number of rows "saved" in step 2.
Step 4: Input the "saved" rows/values at the top of the range.
Step 5: Beginning with the row Count + 1, clear the values from there to the end of the range.

Also, please note that this 5x2 range has other data to the sides of it that I do not want to be effected.

Not that I would turn it down, but I'm not asking for this sub to be written out in full for me.
All I am asking is if you know of any clever ways to preform any part of this process then please let me know and point me in the right direction!

Thank you,
Grummet

 

[mumps]

Try:

Sub MoveCellsUp()
    Dim x As Long
    For x = 5 To 1 Step -1
        If Range("A" & x) = "" And Range("B" & x) = "" Then
            Range("A" & x & ":B" & x).Delete
        End If
    Next x
End Sub

The code assumes your range starts in row 1 and is in columns A and B. You may have to change the code to suit your needs.

 

[Grummet]

Try:

Sub MoveCellsUp()
    Dim x As Long
    For x = 5 To 1 Step -1
        If Range("A" & x) = "" And Range("B" & x) = "" Then
            Range("A" & x & ":B" & x).Delete
        End If
    Next x
End Sub

The code assumes your range starts in row 1 and is in columns A and B. You may have to change the code to suit your needs.

Thanks, but this physically removes the blank cells which is what I want to avoid. So for example, if there was another cell which was =A2 and then cells A2:B2 were deleted it would break and display #REF!