How do I find the last row that contains data where cells may contain empty text “”?

0
votes

I am trying to find the last row of a column.

Every cell in the column has the formula =IF(ISERROR(AVERAGE(F5:G5));"";AVERAGE(F5:G5))

I am using the below VBA code to find the last row.

lastrow= Worksheets("SPONSOR ENGAGEMENT").Cells(Worksheets("SPONSOR ENGAGEMENT").Rows.Count, trendcnt).End(xlUp).Row

However that code also counts cells which have empty text "" resulting from the formula.

How can I count only the cells which have data not empty text?

2
excelvba
thespreadsheetguru.com/blog/2014/7/7/… or stackoverflow.com/questions/71180/… should be of help – Ralph
stackoverflow.com/questions/11169445/… – Scott Craner
Hi Ralph & Scott, thanks for helping me out with those links. I tried this code but it gives me exactly the same result. lastline = Worksheets("SPONSOR ENGAGEMENT").Columns(TRENDC).Find("*", , , , xlByRows, xlPrevious).Row it still counts the empty text "" – cube2016

2 Answers

2
votes

i used Gary's first code and made it fast:

Function LastRowWithNonNullData() as Long
Dim LastRow As Variant
Dim i As Long
LastRow = Range(Cells(1, 1), Cells(Rows.Count, 1)).Value2
For i = Rows.Count To 1 Step -1
        If LastRow(i, 1) <> vbNullString Then
            'MsgBox i
            LastRowWithNonNullData = i
            Erase LastRow
            Exit Function
        End If
Next i
Erase LastRow
End Function

For example on my computer it takes 0,125 secondes (instead of 4,3 secondes) if i only put something in cell "A1".

I Also made it a function, more usefull.

0
votes

You can use this, but it is very slow:

Sub LastRowWithNonNullData()
    Dim LastRow As Variant, i As Long
        For i = Rows.Count To 1 Step -1
            If Cells(i, "A").Value <> "" Then
                MsgBox i
                Exit Sub
            End If
        Next i
    End Sub

This is not quite as slow:

Sub IsThisAnyBetter()
    MsgBox Evaluate("IF(COUNTA(A:A)=0,"""",MAX((A:A<>"""")*(ROW(A:A))))")
End Sub

Both will ignore Nulls at the bottom of column A. If you use the argument to Evaluate() directly in a worksheet cell, it must be array-entered.

EDIT#1:

Both subs work on column A. Say we want to modify the formula in the second sub to work on column 77 (that is column BY). Here is a way to do it:

Sub AnyColumn()
    Dim s1 As String, s2 As String, iCol As Long, iColAlpha As String
    Dim s3 As String, s4 As String, s5 As String

    iCol = 77
    s1 = "IF(COUNTA(A:A)=0,"""",MAX((A:A<>"""")*(ROW(A:A))))"
    s2 = Cells(1, iCol).Address(0, 0)
    s3 = Left(s2, Len(s2) - 1)
    s4 = s3 & ":" & s3
    s5 = Replace(s1, "A:A", s4)

    MsgBox s5
End Sub

EDIT#2:

The formula I used in Evaluate() was adapted from Chip Perason