Find cell before last that is greater than 0

user_13595573 0
Viewed 255

I have the following code in VBA to find the last cell inside a range that is greater than 0:

Set myRange = .Range(.Cells(1, 14), .Cells(1, 23))
count = 0 'Counter
For Each cll In myRange
   If cll.Value > 0 Then
      count = count + 1
      NoZeroDir = cll.Address
   End If
Next

It gets the address of the last cell greater than 0 in that range. But, how could I get the address from the cell greater than 0 before this last one?

I was thinking of using an offset but that way I'd get the cell before the last > 0 but this cell could not be > 0.

To illustrate it a bit, as an example I have:

2 3 5 0 1 7 0 8 1 0 1

The address from the last cell > 0 would be (1,11) but I want the cell before that one > 0, that is (1,9), not (1,10) as this is 0.

cell vba excel
2 Answers

To find the second last number that is >0

Option Explicit

Public Sub FindSecondLastValueGreaterZero()
    Dim MyRange As Range
    Set MyRange = Range("A1:K1")
    
    Const MAXSKIPS As Long = 1  ' skip 1 number that is >0
    Dim Skips As Long
    
    Dim iCol As Long
    For iCol = MyRange.Columns.Count To 1 Step -1
        If MyRange(1, iCol).Value > 0 And Skips < MAXSKIPS Then
            Skips = Skips + 1
        ElseIf MyRange(1, iCol).Value > 0 Then
            Debug.Print "Found at: " & MyRange(1, iCol).Address
            Exit For
        End If
    Next iCol
End Sub

This will start in K loop backwards until it finds a 0 then keeps doing it until skipped >0 is 1 and print the address I1 as result.

enter image description here

Since this loops backwards from right to left it should find the result (in most cases) faster than your code.

user_32196130

Alternative using Worksheetfunction Filter() (vs. MS 365)

Based upon the newer WorksheetFunction Filter() (available since version MS/Excel 365) and using OP's range indication

=FILTER(COLUMN(A1:K1),A1:K1>0)

you are able to get an array of column numbers from cells greater than zero (0) via an evaluation of the generalized formula pattern.

If you get at least two remaining columns (i.e. an upper boundary UBound() > 1) you get the wanted 2nd last column number by i = cols(UBound(cols) - 1) and can translate it into an address via Cells(1, i).Address.

Public Sub SecondLastValGreaterZero()
    'a) construct formula to evaluate
    Const FormulaPattern As String = "=FILTER(COLUMN($),$>0)"
    Dim rng As Range
    Set rng = Sheet1.Range("A1:K1")           ' << change to your needs
    Dim myFormula As String
    myFormula = Replace(FormulaPattern, "$", rng.Address(False, False, external:=True))
    'b) get tabular column numbers via Evaluate
    Dim cols As Variant
    cols = Evaluate(myFormula)
    
    'c) get the 2nd last column number of cell values > 0
    Dim i As Long
    If Not IsError(cols) Then
        If UBound(cols) > 1 Then i = cols(UBound(cols) - 1)
    End If
    
    'd) display result
    If i > 0 Then
        Debug.Print "Found at column #" & i & ": " & Cells(1, i).Address
    Else
        Debug.Print "Invalid column number " & CStr(i)
    End If
End Sub

Example result in VB Editor's immediate window

Found at column #9: $I$1
user_64602970
Related