0

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?

Community
  • 1
  • 1
cube2016
  • 5
  • 2
  • http://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba or http://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro should be of help – Ralph Feb 18 '16 at 16:15
  • 1
    http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba – Scott Craner Feb 18 '16 at 16:16
  • 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 Feb 18 '16 at 16:38

2 Answers2

2

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.

Patrick Lepelletier
  • 1,596
  • 2
  • 17
  • 24
0

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

Community
  • 1
  • 1
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Hi Gary,this code seems to be working perfectly and fast. But i do have one more question. how can i pass a range like Cells(initc, trendcnt), Cells(Target, trendcnt) where initc, trendcnt, target are variables? – cube2016 Feb 18 '16 at 17:04
  • @cube2016 You would need to splice together a new string for *Evaluate()* to process based on your parameters. – Gary's Student Feb 18 '16 at 17:22
  • Hi Gary, I would be really grateful if you can provide a small example for your solution where I can use variables. example : I have to read the range A1:A2 but I want to provide it as range(cells(1,1),cells(1,2)) – cube2016 Feb 18 '16 at 17:26
  • @cube2016 See my **EDIT#1** – Gary's Student Feb 18 '16 at 17:48
  • Gary, Worked like a charm. Thanks a lot. Can you explain me what exactly the function does in Evaluate("IF(COUNTA(A:A)=0,"""",MAX((A:A<>"""")*(ROW(A:A))))") so that I can expand it for other stuff – cube2016 Feb 18 '16 at 18:10
  • @cube2016 See my **EDIT#2** – Gary's Student Feb 18 '16 at 18:31