Excel VBA to determine last non-value (IE may have

Question

I have a column that has a formula in each row field. The formula propagates data from another excel spreasheet. If there's nothing in the row field, though, the row remains blank.

I have found many examples on google to get the last row in a column. However, they fail, because they detect the formula as the row having something in it. That makes sense. However, how can I get the last row in a column that ignores the formula and only attempts to detect values in the column fields?

I'm currently using two methods for searching for the last row in a column of which both fail:

Function lastRowA(rngInput As Range) As Variant
    Dim WorkRange As Range
    Dim i As Integer, CellCount As Integer
    Application.Volatile
    Set WorkRange = rngInput.Rows(1).EntireRow
    Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
    CellCount = WorkRange.Count
    For i = CellCount To 1 Step -1
        If Not IsEmpty(WorkRange(i)) Then
            lastRowA = WorkRange(i).Value
            Exit Function
        End If
    Next i
End Function

and

function lastRow(column as string, optional plusOne as boolean)
    If (plusOne = False) then
        plusOne=False
    End If

    if (plusOne = False) Then
        lastRow = Replace(Range(column & "65536").End(xlUp).Address, "$", "")
    Else
        lastRow = Range(column & "65536").End(xlUp).Address
        lastRow = Cells(lastRow)
        ' Replace(, "$", "")
    End If
End Function

Answer 1

I've never done anything quite like this, but it seems to work correctly and quicky for fairly large areas. Even though you said the column is all formulas, this accounts for a mix of values and formulas, thus the outer loop stepping backwards through the Areas:

Function GetLastFormulaBlank(rngInput As Excel.Range) As Excel.Range

Dim rngFormulas As Excel.Range
Dim rngArea As Excel.Range
Dim CellCounter As Long
Dim AreaCounter As Long
Dim varAreaCells As Variant
Dim rngLastFormulaBlank As Excel.Range

Set rngFormulas = rngInput.SpecialCells(xlCellTypeFormulas)
For AreaCounter = rngFormulas.Areas.Count To 1 Step -1
    Set rngArea = rngFormulas.Areas(AreaCounter)
    varAreaCells = rngArea.Value2
    If IsArray(varAreaCells) Then
        For CellCounter = UBound(varAreaCells) To LBound(varAreaCells) Step -1
            If varAreaCells(CellCounter, 1) = "" Then
                Set rngLastFormulaBlank = rngArea.Cells(CellCounter)
                Exit For
            End If
        Next CellCounter
    Else
        If varAreaCells = "" Then
            Set rngLastFormulaBlank = rngArea.Cells(1)
        End If
    End If
    If Not rngLastFormulaBlank Is Nothing Then
        Exit For
    End If
Next AreaCounter

Set GetLastFormulaBlank = rngLastFormulaBlank
End Function

You'd call it like this:

Sub test()
Dim rngLastFormulaBlank As Excel.Range

Set rngLastFormulaBlank = GetLastFormulaBlank(ActiveSheet.Range("A:A"))
If Not rngLastFormulaBlank Is Nothing Then
    MsgBox rngLastFormulaBlank.Address
Else
    MsgBox "no formulas with blanks in range"
End If
End Sub

Answer 2

Here's a simple way to find the last cell in a column that does not contain a formula. It will be 0 if there is no cell without a formula.

Sub Test()

Dim i As Long, tempLast As Long, lastRow As Long
tempLast = Range("A" & Rows.Count).End(xlUp).Row

For i = tempLast To 1 Step -1
    If Len(Cells(i, 1)) <> 0 Then
        If Not Cells(i, 1).HasFormula Then
            lastRow = i
            Exit For
        End If
    End If
Next

MsgBox lastRow
End Sub

Note that you should use "rows.count" and not 65536 as that is no longer the last row in the newer versions of Excel. Rows.count work no matter the version, or user settings. Usedrange should also be avoided since there is a weird bug where you need to refresh the usedrange or you'll get erroneous results.

Answer 3

If you want to find the last row that contains a non-blank value (either produced by a formula or by entering a constant) try this

Sub FindLastValue()
Dim jLastRow As Long
jLastRow = ActiveSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Sub