How to get the Last Row in VBA(The Right Way!)

Select("Paul").Kelly As XLMaster

Sir if data is long or short Lastrow how to automatically select lastrow when generated PDF report ۔۔so how does it's work ۔۔

absolutely brilliant.
note that we can use Clear All to remove formating rather than having to close the whole WorkBook? :-)

Thanks. Very didactic. Nerver thinked about using find this way.
One cave at with find is hidden rows and hidden columns. If you have some of them find just seems to ignore them.

This the first video that has genuinely made me interested in the course. I'm just beginning with VBA and the info is so disjointed typically

Link to download the source code does not work (points to monstercampaigns. and hangs) . Should be fairly easy to convert what you showed in the video to functions but I'd be curious to see the original code.

Amazing must watch it again. Are you on linkedin?

Great 💯👍

if filters are turned on you will get the last filtered row, not the last of all rows. Returning the last filtered row could potentially be useful as long as you are aware and not looking for the last actual row of data. Is there a solution for this problem?

Excellent compare and contrast. Do you have advice on how to handle/trap those cases where the range you're testing might be empty?

I would like to know why you are using LookIn:=xlFormulas and not LookIn:=xlValues ​​because xlFormulas returns cells containing functions?

Function LastRow(Optional SpecificCol As Variant, Optional TargetSht) As Long
Dim wsf As WorksheetFunction: Set wsf = WorksheetFunction
'....................................................................................................
Dim tws As Worksheet, temp_LRow, LRow, c As Long

Select Case TypeName(TargetSht)
Case "Error"
Set tws = ActiveSheet
Case "String"
On Error Resume Next
Set tws = Sheets(TargetSht)
If Err Then
Err.Clear
LastRow = CVErr(xlErrRef)
Exit Function
End If
Case "Worksheet"
Set tws = TargetSht
End Select
'....................................................................................................
With tws
On Error Resume Next
temp_LRow = .UsedRange.Rows(.UsedRange.Rows.Count).Cells.Row
If Err Or temp_LRow = 1 Then GoTo none:

If IsMissing(SpecificCol) Then
For LRow = temp_LRow To 1 Step -1
If wsf.CountA(.Rows(LRow)) <> 0 Then
Exit For
End If
Next LRow
Else
If wsf.CountA(.Columns(Columns(SpecificCol).Column)) = 0 Then
GoTo none:
End If
If .Columns(Columns(SpecificCol).Column).SpecialCells(xlCellTypeVisible).Rows.Count = .Rows.Count Then
LRow = .Cells(Rows.Count, Columns(SpecificCol).Column).End(xlUp).Row
Else
For LRow = temp_LRow To 1 Step -1
If wsf.CountA(.Cells(LRow, Columns(SpecificCol).Column)) <> 0 Then
Exit For
End If
Next LRow
End If
End If
End With

If LRow <> 0 Then
LastRow = LRow
Else
none: Err.Clear: LastRow = 1
End If

End Function

Why don't I find the object "shJagged" in my Object catalogue. Why is this?

tsk you so much :D

How can I find the last row if there is an expanded array formula in the longest column?

Very useful

I've always used CurrentRegion and never thought of this. Thanks!

This is incredible, and just what I've wanted for some time. It looks like you could also use xlNext to get the top right cell, because with jagged data, starting in A1 is not always a given!

I'm tempted to make my own Data Class that finds the first and last cells, makes that the range, etc. They you don't have to faff about Diming variables for all these things.

Update This is really useful for filling a column with a formula. EndUp lets you know where to start filling from, but Find is probably the most reliable way to know where to finish.

i am not able to see code at given link

does it work in listbject ?

that's a lot of lines of code to just find the last row /column.

Unfortunately If filtermode of the sheet is true then the last values won`t be the real last values.

Thank you so much! you dont have idea on how much this will help me

Thank you for this very helpful video. I think the lastRow function is especially handy when you want to enter new data. But then again if you work with named Data Ranges in Excel, it can automatically add the last entered data if you are using a form

Thanks

Thank you so much for sharing this great video. The knowledge you share is priceless.

Thank you.

Interesting as always, but I do wonder if this is a bit of a sledgehammer to crack a nut. When reading in data that may have lots of blank cells, I design the sheet so that it has a row of headers at the top, with no gaps, and then a "ref" or similar field in the first column which is non-empty for every valid record, and a blank cell denotes the end of the data. (one could automate this of course, using code on the sheet to detect when changes are made and check column 1 is non empty, unless the cell below contains "ZZZ"). Then I use something like set rg = range(sh1.range("top_left").end(xltoright), sh1.range("top_left").end(xldown)), although I think current region would work fine and normally give the same results (except where one has a row with a blank in column 1...).

Wow! excellent video. Instant sub. Thanks!

2023

Great Video ! as always! Just can't download the Code, cause the site isnt loading. Update: it was my adblock. Your site ist now "excepted" :D

Paul, this was an excellent video, thanks very much. However, the download link doesn't seem to be working, tried it for the last 3 days, just "spins".

Thanks

how to do ctrl+arrow down as loop until last cell found in vba? that would be appreciated

Most of the macros I manage were inherited, and thankfully they have column A fully populated with no spaces.

However, they were written in the days when there were only 65,536 rows. Recently, there was data that went past 70,ooo rows and it decided that the last row was row #1 until I updated it.

At first, I was skeptical of a better way, but using Find looks like a good way of doing it regardless of sheet length. Thanks for sharing!

Hi, i need a help....how can i make range a varible insted of using Range("A1:D4") ...i want A,1,D,4 all must be daynamic variable
kindly support

Download link in the e-mail received doesn't seem to work (anymore?).

Excellent presentation Paul !

wow, I'm 62 and just getting into VBA, not sure I'm getting into and EOL language.
I looked at all your options and thought I should share one I use for selecting report print areas
Is it simpler than your option ?
It will fail with jagged data, correct ?

'Declare variables
Dim startcell As Range
Dim Lastrow As Long
Dim lastcol As Long
Dim sh As Worksheet

'Set objects
Set startcell = Range("B2")
Set WS = Sheets("data")

'Find last row and column with data
Lastrow = WS.Cells(WS.Rows.Count, startcell.Column).End(xlUp).Row
lastcol = WS.Cells(startcell.Row, WS.Columns.Count).End(xlToLeft).Column

'Select the dynamic range
WS.Range(startcell, WS.Cells(Lastrow, lastcol)).Select

Thanks !!

How to find last row of printing page

Actually, this is a fabulous but heavy-to-digest lesson. I hope that you have used explicit variables and meaningful sheet names and did not use the dot style for writing the collections of the functions to make it easier for us to understand. Overall, Great work. Thanks.

Hi - Find method doesn't work if data on a sheet is filtered, in that case we need to clear the data but since sheet is protected so find will also not work, so we can write another function based on usedrange and counta worksheet function that will work in each situation

I need to use the same program on different computers. It works fine in one but some codes not working in the other.Especially
if the codes starting with "application" then its not working.why could it be.

When I tried the "best" (i.e. the "find") solution it didn't work for my last column. My last column was W but it reported U. (V was empty). When I examined it I saw that some of the W cells were merged with X cells. When I un-merged W & X it worked. While I would understand if it had reported W or X, I'm not sure why the find method didn't report either of those columns and stopped at U instead. So this is certainly a drawback to that method.

could you please tell what's the screen recording tool you used? it seems perfect.

The link to the code is not loading. Any chance it will get fixed?

Thanks for the great content. Where was this video 15 years ago?