Get last used row in worksheet

[AlexanderBB]

According to various posts this should work
LastRowNum = Cells(Rows.Count, 1).End(xlUp).Row
In my sheet it returns 27 even though there is data in Row 28 but not in Column 1.
Is there a method to find the last row with data in any column? It could be any col from A to S.

 

[Holger]

try this:

=SUMPRODUCT(MAX((P:P<>"")*ROW(P:P)))

you need to adjust P in my example to match your row you are looking at....

 

[AlexanderBB]

Thanks @Holger, can you tell me how to run that in a module? I think it's something like App.WorksheetFunction = .
I've never use it before.

 

[Holger]

Sorry, I would not know, as I have equally never used in VBA.

 

[Alex Blakenburg]

If you don't have a column that is reliably always going to have data in it then the Find method tends to be the preferred method.

    Dim LastRowNum  As Long
    LastRowNum = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
                    
    Debug.Print LastRowNum

 

[AlexanderBB]

Thank you Alex, that does it.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.