excel-vba Find Last Row Using Named Range


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

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
    MsgBox "Last row of 'MyNameRange' under Sheets 'form': " & rwMax
End Sub