Find Last Row in a Column OR a Table Column(ListObject) by range
Finding the last row requires:
This proposed solution is more general, requires only the range ,less chance of typos and is short (just calling MyLastRow
function).
Sub test() Dim rng As Range Dim Result As Long Set rng = Worksheets(1).Range("D4") Result = MyLastRow(rng) End Sub
Function MyLastRow(FirstRow As Range) As Long
Dim WS As Worksheet
Dim TableName As String
Dim ColNumber As Long
Dim LastRow As Long
Dim FirstColumnTable As Long
Dim ColNumberTable As Long
Set WS = FirstRow.Worksheet
TableName = GetTableName(FirstRow)
ColNumber = FirstRow.Column
''If the table (ListObject) does not start in column "A" we need to calculate the
''first Column table and how many Columns from its beginning the Column is located.
If TableName <> vbNullString Then
FirstColumnTable = WS.ListObjects(TableName).ListColumns(1).Range.Column
ColNumberTable = ColNumber - FirstColumnTable + 1
End If
If TableName = vbNullString Then
LastRow = WS.Cells(WS.Rows.Count, ColNumber).End(xlUp).Row
Else
LastRow = WS.ListObjects(TableName).ListColumns(ColNumberTable).Range.Find( _
What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If
MyLastRow = LastRow
End Function
''Get Table Name by Cell Range
Function GetTableName(CellRange As Range) As String
If CellRange.ListObject Is Nothing Then
GetTableName = vbNullString
Else
GetTableName = CellRange.ListObject.Name
End If
End Function