Code to automatically move rows to the Last empty cell based on conditions

[Pasadu]

Good day Sir. I want a code whereby when a cell in Column A is not empty, that whole row should move to the last row of the data, and the rows with data should move up to fill the new empty row.
Let's say there are 10 rows with information. When cell A2 is not empty, row 2 should automatically move to the last row(A11). And all rows beneath A2 should move up to fill the row 2 that is currently empty.
So that, i will always have the rows above where cells in column A are empty. And where cells in Column A are not empty, they shall be the rows below.
In summary, it is like sorting. If cells in column A are empty, those rows should stay above.
If cells in column A are not empty, those rows should stay below.

 

[BobBridges]

I'm not following part of this. Let me rephrase, and you tell me if I have it right: You want a program that will look through all the used rows in a worksheet. Any row that has no data in col A should be moved to the bottom (and the rest of the rows moved up to fill its place), until all the blank-in-A rows are at the bottom. Is that right?

 

[Pasadu]

Any row that has no data in col A should be moved to the top (and the rest of the rows moved up to fill its place). I have explained it in the attachment. Thank You.

 

[BobBridges]

Oh, sorry, you're right; I got it backward. So if I teach you how to write this program, do you already know something about VBA? How much of the program have you written already?

 

[Pasadu]

Oh, sorry, you're right; I got it backward. So if I teach you how to write this program, do you already know something about VBA? How much of the program have you written already?

I don't know how to write codes, but I have interest in learning VBA. Currently I haven't written any code. Thank you.

 

[BobBridges]

Well, we'll start slow, then. First, let's write a program that does two things:
1) Say hello to you (just for fun, so you see how that works)
2) Points the cursor to A5 (again, just so you can see how it's done.
Such a program, in a simple form, looks like this:

Sub Main()
  MsgBox "Hello, Pasadu!"
  ActiveSheet.Cells(5,1).Select
  End Sub

Once you've got that working I'll explain how the parts work. But wait—do you know where to copy that code?

 

[Pasadu]

Well, we'll start slow, then. First, let's write a program that does two things:
1) Say hello to you (just for fun, so you see how that works)
2) Points the cursor to A5 (again, just so you can see how it's done.
Such a program, in a simple form, looks like this:

Sub Main()
  MsgBox "Hello, Pasadu!"
  ActiveSheet.Cells(5,1).Select
  End Sub

Once you've got that working I'll explain how the parts work. But wait—do you know where to copy that code?

Thank you for your time in guiding me through VBA. I have a screenshot of it. I copied it to the workbook But when the cursor points to A5, i dont see anyhting.

 

[BobBridges]

You don't have to point the cursor to A5; the program first displays that Hello message to you, then the program itself changes the selection to A5. If you move the Selection to a different cell and run the program again, it should display the Hello message again, then select A5 again. If the program is doing that, then we're ready to take another step.

But what should that next step be? Well, the program is going to have to know where the bottom row is, so that it can move rows there. So I guess that's what we'll do next.

Wait, I just noticed there's a problem with the way I was going to do this. I had in mind having the program go down the rows, and any time it found a cell in col A that had data in it, I'd move that row to the bottom. But what if there are hundreds or thousands of rows, and they all have data in col A? The program doesn't have to do anything in that case, yet it would move all the rows, one at a time, to the bottom, and you'd have to sit there watching it work, possible for a long time. The same problem if you start from the bottom and look for rows with blank cells in col A, only in reverse.

How to do it, then? This is unexpectedly complicated; the program sounded so simple at first.

I guess I should ask: How many rows might there be in this worksheet? If only a dozen or so, then the waste of time doing it the simple way will never be noticeable; if there are thousands, we need to make the program smarter.

Hey, wait a moment! Why don't you just sort the data on col A? All the blanks will go to the top, then.

 

[Pasadu]

You don't have to point the cursor to A5; the program first displays that Hello message to you, then the program itself changes the selection to A5. If you move the Selection to a different cell and run the program again, it should display the Hello message again, then select A5 again. If the program is doing that, then we're ready to take another step.

But what should that next step be? Well, the program is going to have to know where the bottom row is, so that it can move rows there. So I guess that's what we'll do next.

Wait, I just noticed there's a problem with the way I was going to do this. I had in mind having the program go down the rows, and any time it found a cell in col A that had data in it, I'd move that row to the bottom. But what if there are hundreds or thousands of rows, and they all have data in col A? The program doesn't have to do anything in that case, yet it would move all the rows, one at a time, to the bottom, and you'd have to sit there watching it work, possible for a long time. The same problem if you start from the bottom and look for rows with blank cells in col A, only in reverse.

How to do it, then? This is unexpectedly complicated; the program sounded so simple at first.

I guess I should ask: How many rows might there be in this worksheet? If only a dozen or so, then the waste of time doing it the simple way will never be noticeable; if there are thousands, we need to make the program smarter.

Hey, wait a moment! Why don't you just sort the data on col A? All the blanks will go to the top, then.

Thank you for your time and patience. I checked again, the first code you gave to me works as you explained..

Also, i want to use excel in a different way, that's why I thought of getting this code if possible, not data sorting. Additionally, I don't know the last row number since the data will keep on being updated.

 

[BobBridges]

That's not a problem; your VBA program can find the last row easily enough. Ok, let's teach the program to do that next. Take a look at this program and tell me how it works:

Sub Main()
  Set oc = ActiveSheet.Cells(2, 2)
  Select Case True
    Case oc.Value = "": lastrow = 1
    Case oc.Offset(1, 0).Value = "": lastrow = oc.Row
    Case Else: lastrow = oc.End(xlDown).Row
    End Select
  MsgBox "The last row is " & lastrow & "."
  End Sub

This little program finds the last row in your worksheet. But it makes several assumptions that may or may not be valid:

1) That when you run the program, the ActiveSheet is the one you want. If it's the only worksheet in your workbook, that shouldn't be a problem.

2) That the worksheet has one header row that you're ignoring.

3) That there's data in every cell of column B. If there's a blank cell in the second column, this routine will think the last row is before it.

The Select statement looks at three possibilities:
a) If B2 is empty, then the last row is the header row itself; the rest of the worksheet is blank.
b) If B3 is empty, then the last row is 2.
c) Otherwise it does an <End><Down> function to jump to the last cell that has data, and reports that as the last row.

Try it out and tell me whether it works for you. Ask questions.