Shortest Code for finding last row via VBA

[nbrcrunch]

I'm wondering why the following doesn't seem to be encouraged on ANY Excel site I've ever seen. I've seen lots of code for determining the last row but this is the shortest. Is it error prone?

Sub Macro3()

Selection.SpecialCells(xlCellTypeLastCell).Select
TheLastRow = ActiveCell.Row
MsgBox (TheLastRow)

End Sub

 

[Richard Schollar]

Depends whether you want the last row of actual data which that code won't necessarily find, or the last row containing modified formatting (which in my tests your code evaluates correctly). I think on some versions of Excel, the xlCellTypeLastCell may not get updated until you save the workbook. Not sure about this, but if it is the case, it could well give an incorrect value if you have gone and deleted a lot of your data.

 

[Norie]

That's the shortest code you've seen.

Msgbox Range("A" & Rows.Count).End(xlUp).Row

And yes I would think that code might be error prone, mainly because of the use of Selection.

 

[VoG]

I don't think that's the shortest (in terms of typing) and this should be quicker

Sub Macro3()
Dim TheLastRow As Long
TheLastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
MsgBox (TheLastRow)
End Sub

 

[Joe4]

It has some shortcomings, in that it does not always update right away.

For example, run it on any file. Now, delete the last few rows and try running it again. You'll notice that it doesn't update. Save the file and run it again, and then it returns the updated value.

BTW, you can shorten your code a little (no need to use select statements):

TheLastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
MsgBox TheLastRow

or even

MsgBox ActiveCell.SpecialCells(xlCellTypeLastCell).Row

 

[Vancity]

Good post, I use this quite often so I was curious what the answer to the question is. I searched and found this:

Function LastRow() As Long
LastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
End Function

Any problems with this function?
*** Found a problem: only searches visible cells, so if you're using autofilter it won't work

 

[xenou]

I'm noticing that the original formula in the first post does not show the correct "last row" if you delete or clear rows at the bottom (i.e., even after saving). So its definitely suspect. Vog's additions of UsedRange makes a big difference!

Also, the behavior regarding the function just posted using .Find() --not finding hidden rows-- is also true for the other solutions here...although I still use that one a lot myself. --AB

 

[nbrcrunch]

Live & learn. I've had so many false starts with VBA that I finding myself back at square one every time I pick it up again. Its more related to whatever the current needs of the business are. I really need to set my own priorities and stop going on every whim of whoever is my boss at the time.

 

[anita_bath]

Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row

 

[toddnestor]

The easiest way I have, which doesn't throw out errors if you use the wrong version of excel as far as I can tell, and doesn't rely on any special functions is this:

Sub FindLastRow()

Dim x, y As Integer

x = 3 ' column number to start count, this will count down in the referenced column to search for data (3 = C, 1 = A, 5 = E, etc.)
y = 7 'row number ' row number to start on, if you have blank rows at the top you can start down lower like I did here

Do Until Cells(y + 1, X).Value = ""
y = y + 1
Loop

Msgbox y

End Sub