excel-vba Methods for Finding the Last Used Row or Column in a Worksheet Find Last Row Using Named Range
Example
In case you have a Named Range in your Sheet, and you want to dynamically get the last row of that Dynamic Named Range. Also covers cases where the Named Range doesn't start from the first Row.
Sub FindingLastRow()
Dim sht As Worksheet
Dim LastRow As Long
Dim FirstRow As Long
Set sht = ThisWorkbook.Worksheets("form")
'Using Named Range "MyNameRange"
FirstRow = sht.Range("MyNameRange").Row
' in case "MyNameRange" doesn't start at Row 1
LastRow = sht.Range("MyNameRange").Rows.count + FirstRow - 1
End Sub
Update:
A potential loophole was pointed out by @Jeeped for a a named range with non-contiguous rows as it generates unexpected result. To addresses that issue, the code is revised as below.
Asumptions: targes sheet = form, named range = MyNameRange
Sub FindingLastRow()
Dim rw As Range, rwMax As Long
For Each rw In Sheets("form").Range("MyNameRange").Rows
If rw.Row > rwMax Then rwMax = rw.Row
Next
MsgBox "Last row of 'MyNameRange' under Sheets 'form': " & rwMax
End Sub
