Find last row of column without including blank cells

Another Last Row Issue

Intro

• An empty cell is an empty cell.
• A blank cell is either an empty cell, a cell containing a formula evaluating to "", a cell containing a ', etc. (if any).
• If you want to select the non-blank cells, you have to use the xlValues parameter of the LookIn argument of the Find method. The parameter of this argument is saved each time you use the method. So by sheer chance, the accepted answer is working correctly since the parameter is set to xlValues (which is not the default value).
• 'Proper' data does not contain blank rows at the bottom, so most often the xlFormulas parameter of the LookIn argument is the way to go.
• In-depth studying of the Find method is crucial to revealing the countless possibilities.
• The Find method may (will) fail if the worksheet is filtered and additionally may (will) fail with the xlValues parameter when rows are hidden (the xlFormulas parameter will handle hidden rows correctly).

Tip

• This is a link to Siddharth Rout's legendary answer to the question Error in finding last used cell in Excel with VBA which is worth (highly recommended) studying as is the following.

The Examples

Option Explicit

' All four solutions assume that there are no hidden or filtered rows.

Sub testLastRowBeginner()
    
    Const FirstRow As Long = 2

    Dim lCell As Range
    Set lCell = Columns(20).Find("*", , xlValues, , , xlPrevious)
    
    ' Assuming that there is data at least in row 2 ('FirstRow') of the column.
    Dim LastRow As Long: LastRow = lCell.Row
    
    MsgBox "The Last Row of Data for Column T " & LastRow
    
    With Range("B" & FirstRow & ":T" & LastRow).SpecialCells(xlCellTypeVisible)
        With .Font
            .Color = -16776961
            .TintAndShade = 0
        End With
    End With
    
End Sub


Sub testLastRowIntermediate()
    
    Const FirstRow As Long = 2

    Dim lCell As Range
    
    ' For non-empty cells (most often):
    'Set lCell = Columns(20).Find("*", , xlFormulas, , , xlPrevious)
    
    ' For non-blank cells which is most probably your case,
    ' because you have formulas evaluting to "":
    Set lCell = Columns(20).Find("*", , xlValues, , , xlPrevious)
    
    Dim LastRow As Long
    If Not lCell Is Nothing Then
        LastRow = lCell.Row
        If LastRow < FirstRow Then
            MsgBox "The last row is smaller then the first.", _
                vbCritical, "Last Row Too Small"
        End If
    Else
        MsgBox "The column range is blank.", vbCritical, "No Last Cell"
        Exit Sub
    End If
    
    MsgBox "The Last Row of Data for Column T " & LastRow
    
    With Range("B" & FirstRow & ":T" & LastRow).SpecialCells(xlCellTypeVisible)
        With .Font
            .Color = -16776961
            .TintAndShade = 0
        End With
    End With
    
End Sub

Sub testLastRowAdvanced()
    
    Const First As String = "T2"

    With Range(First)
        Dim lCell As Range
        Set lCell = .Resize(.Worksheet.Rows.Count - .Row + 1) _
            .Find("*", , xlValues, , , xlPrevious)
        If lCell Is Nothing Then
            MsgBox "No data.", vbExclamation, "Fail"
            Exit Sub
        Else
            LastRow = lCell.Row
        End If
    End With
    
    MsgBox "The Last Row of Data for Column T " & LastRow
    
    With Range("B" & FirstRow & ":T" & LastRow).SpecialCells(xlCellTypeVisible)
        With .Font
            .Color = -16776961
            .TintAndShade = 0
        End With
    End With
    
End Sub

Sub testLastRowExpert()
    
    Const Cols As String = "B:T"
    Const FirstRow As Long = 2

    Dim rg As Range
    With Columns(Cols).Rows(FirstRow)
        Dim lCell As Range
        Set lCell = .Resize(.Worksheet.Rows.Count - FirstRow + 1) _
            .Find("*", , xlValues, , xlByRows, xlPrevious)
        If lCell Is Nothing Then
            'MsgBox "No data.", vbExclamation, "Failure"
            Exit Sub
        End If
        Set rg = .Resize(lCell.Row - .Row + 1)
    End With
    'Debug.Print rg.Address
    
    With rg.SpecialCells(xlCellTypeVisible)
        With .Font
            .Color = -16776961
            .TintAndShade = 0
        End With
    End With
    
End Sub

You can use the VBA function find() to solve the problem using the following code

lastRow = ActiveSheet.Range("T:T").Find("*", searchOrder:= xlByRows, SearchDirection:= xlPrevious).Row

"*" means find any value, so it is possible to locate any cell with a value.

searchORder:= xlByRows means that the function will find the value row by row.

searchDirection:= xlPrevious means that the function will look from the end of the sheet to the beginning of the sheet.

So the entire function's parameter means that to find a cell with value in column T, row by row from the end of the sheet to the top of the sheet.

Use .Row attribute to obtain the row number, use +1 to find the empty row.