Find First / Last Empty Row

Hi All,

Would anyone know how to search for the "First Empty Row" in a VB Macro?
And if possible, can this be copied into a variable (maybe called: FirstEmptyRow) in the format of: $A$31 (for Row 31) or $A$99 (for Row 99), for example?

(The reason for this, is because I would like to make a macro that would search for the first unused row, then append C:\append.csv to that particular row, in column A)

Thanks so much,
CG

Re: Finding The First Empty Row In A Macro

Quote from turtle44

The easiest way would be to look at the "Possible Answers" above. I'm sure they answer the same question. A simple search would work also.

Quote from turtle44

The easiest way would be to look at the "Possible Answers" above. I'm sure they answer the same question. A simple search would work also.

Thanks Turtle. These were the only two I could find when doing the search before posting.

...Since I consider myself fairly new with VBMacro scripting, I wasn't able to come up with an answer based on those searches. My closet attempt was this:

Dim eCell   As  Range 
With Range("A1:A9999") 
    Set FirstEmptyRow = .SpecialCells(xlCellTypeBlanks).Cells(1, 1) 
     MsgBox eCell.Address 
End With

The problem is that I'd like to search for the first row, but not the first empty cell, since some rows will have some blank cells within the row. But then, if possible, I'd love it if I can then assume that column A can be marked as the first cell within that first empty row.

Re: Finding The First Empty Row In A Macro

Quote from turtle44

Are you trying to find the next row after the end of your data, or do you have blank rows in between your data?

Thankfully there should be no blank rows in between (I imagine that would be much harder), so this would be the next row after the end of the data. However .... if that is done, to make sure that the text won't append in one accidental empty row (followed by a occupied row) probably would be good in that scenario, but if that's too hard to figure out, that doesn't need to be done. --- So in summary, if it's easily doable, then please, but if it's too much work, it's not 100% 'needed'. Thanks again, CG

Re: Finding The First Empty Row In A Macro

Quote from turtle44

This will find the last row with any data. (Add 1 to get next empty row) There is no error handling, so this will fail if entire sheet is blank.

Code

lLastRow = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, Lookat:=xlPart, _
         SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

Thanks so much! Will try this out now...

CG