find the last empty row

[lezawang]

Hi I have a form to enter First and Last name and when I click on Enter to enter them, excel will put these 2 names in the last empty row. To grantee the last empty row, there are 2 ways (code). Thanks for your help I got to know them but could not understand them!. The first one

x = WorksheetFunction.CountA(Range("A:A")) + 1
Cells(x, 1) = First.Value
Cells(x, 2) = last.Value

Let say A1:A10 are filled with numbers, except A5 which is empty. Then CountA(A:A) will return 9. so X will be 10 as per above
but row 10 so empty?

There is another way, which I have hard time to understand it. I am sure it is perfect coding but hard to interpret

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1

Any help would be very much appreciate it

 

[KennyGreens]

The first lines of code that you posted return 9 because it is simply counting the cells that aren't empty, it is not finding which one is empty.

The second line that you posted should work fine. Basically, here is a breakdown of what the code says:

Cells(Rows.Count, "A") - It looks at the entire "A" column and counts the total number of rows to start at the last row in the entire column.

End(xlUp) - This is basically like doing ctrl+up arrow in excel, so starting from the last row in column A, it does ctrl+up to find the last row in column A with a value in a cell.

Row + 1 - Simply grabs the row # and adds 1 to it so that you are putting the new value in the last row with value + 1 so as to not overwrite.

 

[Joe4]

There is another way, which I have hard time to understand it. I am sure it is perfect coding but hard to interpret

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1

This is the way that I prefer.

It actually isn't nearly as daunting to understand as it may first appear.

Here are the important components:

Rows.Count
This simply returns the maximum number of rows on an Excel sheet. It new versions of Excel, it is 1048576. In old versions, it is 65536.
The nice thing with using this is that it will work correctly in both old and new version of Excel.

So, what this part:
Cells(Rows.Count, "A")
is saying here is start in the very last cell in column A.
Note that the format of Cells is Cells(row, column).
So the above is the same as saying Range("A1048576") in new version of Excel.
*Note: one advantage to Cells over Range, is that you can use a letter or number for the range reference. So it could also be written as Cells(Rows.Count, 1)

Now, .End(xlUp) is just the equivalent of hitting CTRL+Up Arrow in Excel, which goes up until it finds the first row with data in it
(if you use the Macro Recorder while hitting CTRL+Up Arrow, you can see this).
So, that just find the last cell in Column A with data in it (since we are starting at the bottom, and going up until we find a row with data).

Now that we have arrived at the last cell in column A with data, we use .Row to get the row number of that cell.
That's great, but that is not where we want to paste our data. We want to paste it on the row just below the last one with data.
So we simply add 1 to accomplish that (+1).

Make sense now?


EDIT: As you can see, I am a very slow typist, and Kenny posted an explanation in the meantime.
I hope between both of our explanations, all your questions are answered!

 

[lezawang]

Yes it does make sense now. Thank you all indeed.