MrExcel Publishing
Your One Stop for Excel Tips & Solutions

First Empty Row

Posted by Mike on July 25, 2000 5:40 PM

With VBA code: how can you get to the first empty row below the last row with data. Need to end up in the "A" cell to enter additional numbers.

Than you in advance for any assistance.


Posted by Mike on July 26, 0100 8:44 AM

Hello Ada,
I tried the above code with no success. My first empty row on this paticular report is 5425, but the code takes me to "A2" everytime. There is data in "A2:A5424". I even copied and pasted the code just in case my typing was the problem. Any ideas what I could be doing wrong?

Best regards,


Posted by Ryan on July 26, 0100 1:15 PM


Try this line in a procedure:

FirstEmptyRow = Range("A65000").End(xlUp).Row + 1

Hope this helps,


Posted by Toby on July 26, 0100 1:21 PM

I've found the following works rather well. You end up with a variable (CellA) that tells you the first empty cell. This only drills down the A column. It can be easily modified to write all the empty cells in a column to an array.

Dim Cntr, CellA
Cntr = 1
Do While ActiveCell <> ""
Cntr = Cntr + 1
CellA = "A" & Cntr

Posted by Mike on July 27, 0100 1:43 PM

Problem has been solved. Thanks to everyone for their time, help, and patience.

Best regards,

Posted by Ada on July 26, 0100 4:00 PM

Sorry, I should have tested the code I posted.
Also, the suggestions from Toby and Ryan do not necessarily take you to the cell in column A of the first empty row (since there might be data in lower rows in other columns).

This should do it (I hope):-

firstEmptyRow = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count).Offset(1, 0).Row
Set firstEmptyCell = Range("A" & firstEmptyRow)


Posted by Ada on July 25, 0100 6:39 PM

Than you in advance for any assistance. Mike


FirstEmptyRow = ActiveSheet.UsedRange.Row+1
Set FirstEmptyCell = Cells(FirstEmptyRow, 1)