Excel macro to find the first empty cell in a column

[MrBond]

I've got an excel spreadsheet I'm writing a macro for to process some data files. I have it set up so it prompts the user for a number of data files, then the macro loops through them copying/pasting data to the spreadsheet.

I want it to also number the rows, starting with A2 and going down. This is the code I'm using right now in a test module (it will get integrated into the main module when I get it working).

Dim Row As Long
For Row = 1 To 4
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = Row

I have a column heading in A1 ("Run") and nothing in A2. The macro will not run, however if I add something to A2, the macro runs fine.

I've also tried this line instead of the Range statement:

Columns("A").Cells.SpecialCells(xlCellTypeBlanks).Cells(1).Select

But that line errors out at me saying there's no empty cells in column A. That line is working fine when I refer to column B for another part of the macro.

Any ideas? I want to go to the first empty cell in column A, as simply as possible.

Thanks!

Edit: It works with this line of code:

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

 

[mayest]

Well, I don't know that this is the best way to do it, but this code will calculate the last row in the used range, and then select the cell at that row in column A. It may not be exactly what you need, but maybe it will give you a place to start.

 

[mayest]

Well, that was ugly. Here it is again:

Sub LastCellA()
Dim R As Long
Dim MaxRows As Long
MaxRows = ActiveSheet.Rows.Count
R = ActiveSheet.UsedRange.Rows.Count
If R < MaxRows Then
R = R + 1
Cells(R, 1).Select
Else
Cells(MaxRows, 1).Select
End If
End Sub