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

 

[VoG]

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

Have you timed that versus any of the others?

 

[toddnestor]

Have you timed that versus any of the others?

I haven't actually timed it. It's pretty quick though because it doesn't use the select method. I also know some of the other methods that use xlUp I run into trouble with, like right now I'm running MS Excel 2007 and it didn't like that method.

It'd be interesting to run a time comparison using a large volume of data...

 

[Joe4]

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​

Using Loops is very inefficient. Using the method Norie posted in reply #3 is far more efficient and not subject to Excel version number and shouldn't encounter any errors.

 

[inventiveman]

Using Loops is very inefficient. Using the method Norie posted in reply #3 is far more efficient and not subject to Excel version number and shouldn't encounter any errors.

Unfortunately #3 give the wrong result if there are blank rows in the data.

 

[inventiveman]

Unfortunately #3 give the wrong result if there are blank rows in the data.

OOps! Meant to say blank cells.
ie if Column B uses 10 Rows of data and A10 is a blank cell then this code indicates the Last Row to be 9.
So this code only finds the Last Row in a particular Column - NOT the Last Row used in a WorkSheet.

 

[Michael M]

FWIW
While there are benefits in shorter code, speed for instance.
It's more important that the user understands what the code is actually doing, so that if debugging is required later on, it makes sense.
Hours lost in not being able to debug or update the code, far outweigh the saving of fractions of seconds.
Use what works, and what you understand.

 

[Norie]

You wouldn't use the code in #3 if the column you want to find the last row for is B.

 

[inventiveman]

You wouldn't use the code in #3 if the column you want to find the last row for is B.

True. However I found this thread via Google when trying to find the last Row used in a Worksheet using vba.

Looking back at what's been written, some replies refer to the last row in a column and others to the last row in a worksheet. These 2 different situations, and different code required, could easily cause confusion for some people.

 

[tbro]

It's been a while since this thread was active. However, I've been thinking of a decent solution for finding the very last row/column in a sheet. In some of my cases, I don't know where the last row is located (in what column). And I thought this might happen to others too.

Lastrow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1

Let me break it down, but first I want you to know that UsedRange represents the range in a worksheet that is being used. Quite obvious I know. If you type something in cells A6:A10, UsedRange would be A6:A10.

' UsedRange = A6:A10 in this example
UsedRange.Rows.Count = 5 (A6, A7, A8, A9, A10 = 5 rows)
UsedRange.Row = 6 ' This is the FIRST row (A6) in UsedRange

'So the code would return:
Lastrow = 5 + 6 - 1 = 10

I myself prefer to wrap UsedRange using a With-statement, to make sure I'm checking in the correct worksheet. Might look cleaner too.

With ActiveSheet
     Lastrow = .UsedRange.Rows.Count + .UsedRange.Row - 1
End With

 

[RvG14344]

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.

Thank you for this code. This is what i need.