Last Row

[Scott Huish]

Ok, I just discovered something new to me and I'm wondering if there's any reason why not to do it this way (1st line in example code)?

lr = Range("A:A").SpecialCells(xlCellTypeLastCell).Row
lr2 = Range("A" & Rows.Count).End(xlUp).Row

The 2nd one is a little shorter. Is that its advantage?

 

[Michael M]

I prefer lr3, but this could start a heated debate with other users

lr3=cells(rows.count,"A").end(xlup).row

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

 

[Artik]

As you delve deeper into the topic of searching for the last cell you will see many different ways. Each of them has some advantages, but also some disadvantages. That's why it's a good idea to learn not only these two mentioned ways, but a few more to choose the right method for your current data appearance.

lr = Range("A:A").SpecialCells(xlCellTypeLastCell).Row

If in other columns the data goes further than in column A, you will get the row of the farthest value. E.g. In column A, the last value is in A3, and in column C, for example, the last value is C10. The number returned will be 10, not 3. In addition, if, for example, in column D you have formatted cell D20 (and there is no value in it), the row number returned will be 20. This method can be used when the data in different columns end up in the same row AND the entire range of data has been inserted into a new sheet.

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

If the data is filtered and the last few rows are hidden, the value of the last visible row will be returned, not the last non-empty cell in column A.

Artik

 

[Michael M]

Also, for variable column lengths.....

lr4 = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row

 

[Alex Blakenburg]

Adding to @Artik's comment on the SpecialCells method

lr = Range("A:A").SpecialCells(xlCellTypeLastCell).Row

This mimics the behaviour of using Ctrl+End and finds the bottom right corner of the UsedRange.
An additional downside of this is that if you delete rows, it does not reset the bottom corner until either the workbook is saved or the UsedRange property is used in the code. (same for clearing content at the bottom that should be reducing where the last row falls)

 

[Alex Blakenburg]

The find method is a impacted by a filter being applied but not rows that are manually hidden.

 

[MARK858]

As @Artik has stated .SpecialCells(xlCellTypeLastCell) detects formatting as well as values and so I avoid it

Sub xxxx()
Dim lr As Long
lr = Range("A:A").SpecialCells(xlCellTypeLastCell).Row
MsgBox lr
End Sub

 

[Burrgogi]

As a VBA newbie, reading this thread has been very educational! Thanks guys.

 

[Artik]

The topic is as long as a river. For example, a recent variation on the theme:

Last Row in range but ignore numbers

I have the following code for finding the last row in a range Dim LDR1 As Long: LDR1 = .Range(.Cells(5, "C"), .Cells(150, LDC1)).Find("*", , , , xlByRows, xlPrevious).Row How can I modify this to ignore numbers or non-text items, or do I need a new formula?

www.mrexcel.com

Artik