Difference finding last row in VBA

Question

I have 3 ways of finding last row in col A. What is the diffrence? And which one is the best to use.

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

Nr 2
Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Nr 3
Dim Col, row, lastRow As Integer

Col = 1
row = 1
lastRow = 1

lastRow = Cells(Rows.Count, Col).End(xlUp).row
Cells(lastRow, Col).Select

http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba — , Apr 16 '14 at 07:52
http://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro — , Apr 16 '14 at 07:53
me how is right, this question gets asked again and again. There is no right way. All methods have their problems. See http://stackoverflow.com/a/20849875/973283 where I provide a macro that shows the circumstances under which method fails. — Tony Dallimore, Apr 16 '14 at 08:22
Me how I did a search and didnt find a good answer thats why i stated a question. Thx Tony for that link. — PercyN, Apr 16 '14 at 08:48
@PercyN Sitting firmly on the fence, I agree with the commenters that this question has been done to death, and the OP in that none of the existing answers are particularly satisfying. The real answer is that the "best" way depends on your exact situation. Please explain why none of the existing answers satisfy your needs. — chris neilsen, Apr 16 '14 at 09:56
@chrisneilsen I am new at the site and trying to learn why people uses "Just these varibles in this situation", this question wasn´t meant to find the last row just know the differnce. Many of the people has seen the questions many times but in different situations and know were to find it. In this case you sent me some links were I can compare "Different codes" and read the argument they have for using that. So I am very pleased with the answers I gotten here. The more I use this site the more I will learn how to search it and don´t take your time. Excause me if I am using poor english. — PercyN, Apr 16 '14 at 12:31
Using `Find` is the superior approach. As per the first link above. — brettdj, May 13 '14 at 11:04

score 0 · Accepted Answer · answered Apr 16 '14 at 10:09

One way to get the row number of the last filled cell in column A :

Sub LastFilledCellInColumnA()
    Dim wf As WorksheetFunction, nLastRowA As Long
    Set wf = Application.WorksheetFunction
    nLastRowA = Rows.Count
    If wf.CountA(Cells(nLastRowA, 1)) = 1 Then
        MsgBox nLastRowA
        Exit Sub
    End If
    nLastRowA = Cells(Rows.Count, "A").End(xlUp).Row
    MsgBox nLastRowA
End Sub

If column A is empty 1 will be output

Difference finding last row in VBA

1 Answers1