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

Grummet

Board Regular
Joined
Jun 17, 2014
Messages
80
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 A100.00
xx
xx
Deposit B200.00
Deposit C300.00

<tbody>
</tbody>

Into this:

Deposit A100.00
Deposit B200.00
Deposit C300.00
xx
xx

<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 A100.00
xx
Deposit B200.00
Deposit C300.00
xx

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

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,563
Try:
Code:
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

Board Regular
Joined
Jun 17, 2014
Messages
80
Try:
Code:
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!
 

Forum statistics

Threads
1,136,776
Messages
5,677,667
Members
419,711
Latest member
dacrmcvega0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top