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 =
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