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
