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.

Mike


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,

Mike

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

Mike,

Try this line in a procedure:

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

Hope this helps,

Ryan

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
Range("A1").Select
Do While ActiveCell <> ""
Cntr = Cntr + 1
CellA = "A" & Cntr
Range(CellA).Select
Loop

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,
Mike

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

Mike
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)
firstEmptyCell.Select

Ada


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

Than you in advance for any assistance. Mike


Mike

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

Ada