Find last row based on cell contents

Posted by Tom on April 25, 2001 2:15 PM

Hi.

First off, I'd like to compliment you on this resource for Excel. It is really helpful. I've gotten numerous ideas from the comments. Any chance you can archive these by topic - make 'em available for download or put onto a CD. I'd even pay for them. Or, at least be able to sort through them quickly. I know beggars can't be choosers, but I'll ask anyway.

Now, to my question. I've got some data that is downloaded every day, that varies in length, but the last row, Column "A" contains the word "DUMMY" in all cases. I'm a bit stumped as to how to identify this as the last row.

Among other things, I've tried:

FinalRow = [A65536].End(xlUp).Columns(1).Value = DUMMY
(BTW, thanks to Dave & others for the code that yielded the start down this path above)

Given that I'm a beginner, I'm sure that the syntax isn't legit. The value for "FinalRow" ends up being "False". Any help would be appreciated.

Thanks again. Tom

Posted by David Hawley on April 25, 2001 2:29 PM

Hi Tom

Here are three ways. The last will find the FIRST blank row in Column A. It will only work though if there is at least one entry in Column A.

Sub FindLastRow1()
Dim FinalRow As Long
FinalRow = Columns(1).Find(What:="DUMMY", After:=[A1]).Row
End Sub

Sub FindLastRow2()
Dim FinalRow As Long
FinalRow = Range("A65536").End(xlUp).Row
End Sub

Sub FindLastRow3()
Dim FinalRow As Long
FinalRow = Range("A1").End(xlDown).Row
End Sub

Dave

OzGrid Business Applications

Posted by Tom on April 25, 2001 2:44 PM

Sweet! Thanks Dave. It works! The first example was the most appropriate. Tom