xlup is looking for text, but finding formulas that are "", how to convert to look for non-'"/blank cells?

Question

Counter = Cells(Rows.Count, 4).End(xlUp).Row - 1

So I'm using this to look up occupied cells, but it's finding cells that have formulas that are IF's that end as "".

Can I convert this to look up visible text?

Thank you in advance

Answer 1

Last Row Excluding Cells Containing Formulas Evaluating to ""

Using the Find method

• Here is a link to see what Microsoft states about the Find method.
• Here is a link to Siddharth Rout's legendary post about using the Find method to find the last cell (row).
• Note that Counter is 'pointing' one row above the Last Row.

The Code

Sub testGetLastRowInColumn()

    Dim LastRow As Long
    Dim Counter As Long

' Simple, for the ActiveSheet:
    LastRow = getLastRowInColumn("A") ' 1 or "A", both are allowed.
    Debug.Print LastRow

' Proper, for a certain sheet:
    Dim wb As Workbook
    Set wb = ThisWorkbook ' The workbook containing this code.
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")
    LastRow = getLastRowInColumn("A", ws)
    Debug.Print LastRow

' In your case
    LastRow = getLastRowInColumn(4)
    If LastRow > 0 Then
        Counter = LastRow - 1
    End If
    Debug.Print LastRow, Counter
    
' In your case without the function:
    LastRow = Columns(4).Find(What:="*", _
                              LookIn:=xlValues, _
                              SearchDirection:=xlPrevious).Row
    If LastRow > 0 Then
        Counter = LastRow - 1
    End If
    Debug.Print LastRow, Counter

' Or simplified:
    LastRow = Columns(4).Find("*", , xlValues, , , xlPrevious).Row
    If LastRow > 0 Then
        Counter = LastRow - 1
    End If
    Debug.Print LastRow, Counter

' Or just (if you're sure that there is data in column 4):
    Counter = Columns(4).Find("*", , xlValues, , , xlPrevious).Row - 1
    Debug.Print LastRow, Counter

End Sub

Function getLastRowInColumn(ColumnIndex As Variant, _
                            Optional Sheet As Worksheet = Nothing, _
                            Optional includeEmpties As Boolean = False)
    
    If Sheet Is Nothing Then
        Set Sheet = ActiveSheet
    End If
    
    Dim FormVal As XlFindLookIn
    If includeEmpties Then
        FormVal = xlFormulas
    Else
        FormVal = xlValues
    End If
    
    Dim rng As Range
    Set rng = Sheet.Columns(ColumnIndex).Find(What:="*", _
                                              LookIn:=FormVal, _
                                              SearchDirection:=xlPrevious)
    If Not rng Is Nothing Then
        getLastRowInColumn = rng.Row
    Else
        getLastRowInColumn = 0
    End If
    
End Function