Have you ever needed to write an Excel macro that looped through rows and manipulated the data in some manner? For example, you need to delete rows where column C has the value “GREEN”. But you have blank or inconsistent rows, or just a metric ton of data, so you can’t just sort and delete them.

In order to avoid looping through 64,000 rows (which is quite slow, really), you’d want to only go to the last row that contains data. You need to know which row is truly the last row that has values in the cells.

The typical SpecialCells code I found didn’t quite work for me due to blanks and formulas. It ignores rows that have nothing but formulas in them, such as summation rows.

So I went looking for a better solution. I’ve seen this function posted a couple places online. It has helped me several times, so I thought I’d share.

Function LastCell(ws As Worksheet) As Range
Dim lastRow&, LastCol%
On Error Resume Next
With ws
lastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
Set LastCell = ws.Cells(lastRow&, LastCol%)
End Function

You’d call that function like this.

Dim lastRow As Integer
lastRow = LastCell(ActiveWorkbook.ActiveSheet).Row

You could then loop through the rows like this.

Dim rowCounter As Integer
For rowCounter = 1 To lastRow
' do whatever
Next

Happy macroing!