Excel VBA – Finding Last Non-Empty Row Based on Column Header

How about:

Function GetLastRow(strSheet, strColumn) As Long
    Dim MyRange As Range

    Set MyRange = Worksheets(strSheet).Range(strColumn & "1")
    GetLastRow = Cells(Rows.Count, MyRange.Column).End(xlUp).Row
End Function

Regarding a comment, this will return the row number of the last cell even when only a single cell in the last row has data:

Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

I have expanded on my comment above to provide solutions that

• do not use Select
• cater for the last cell in row 1 being used
• cater for the entire row being empty
• cater for the entire row being full

The Find method in the second code is a far more direct method of establishing the first non-blank cell

This line Set rng1 = ws.Rows(1).Find("*", ws.[a1], xlValues, , xlByColumns, xlPrevious) says, start in cell A1 of Sheet "DTCa" then look backwards (ie from the last cell in row 1) in row1 by column looking for anything (the *). This method either find the last non blank or returns Nothing , ie an empty row

using xltoLeft with specific checks

Sub Method1()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("DTCs")
If ws.Cells(1, Columns.Count) = vbNullString Then
    Set rng1 = ws.Cells(1, Columns.Count).End(xlToLeft)
    If rng1.Column <> 1 Then
        'return last used cell
        MsgBox "rng1 contains " & rng1.Address(0, 0)
    Else
    If ws.[a1] = vbNullString Then
            MsgBox ws.Name & " row1 is completely empty", vbCritical
        Else
            'true last used cell is A1
            MsgBox "rng1 contains " & rng1.Address(0, 0)
        End If
    End If
Else
    'last cell is non-blank
    MsgBox ws.Cells(1, Columns.Count) & " contains a value", vbCritical
End If
End Sub

recommended

Sub Method2()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = Sheets("DTCs")
    Set rng1 = ws.Rows(1).Find("*", ws.[a1], xlFormulas, , xlByColumns, xlPrevious)
    If Not rng1 Is Nothing Then
        MsgBox "rng1 contains " & rng1.Address(0, 0)
    Else
        MsgBox ws.Name & " row1 is completely empty", vbCritical
    End If
End Sub