Get last row excluding zero

[YasserKhalil]

Hello everyone
I am trying to get the last row in column B ..
Column B has formulas that may result in zeros ...
I need to find the last row that has non-zero value
Thanks advanced for help

 

[Chihiro]

Something like this?

Sub bCount()
Dim lRow As Long
Dim lvRow As Long
Dim i As Integer

lRow = Range("B" & Rows.Count).End(xlUp).Row

For i = lRow To 1 Step -1
    If Cells(i, 2).Value <> 0 Then
        lvRow = i
        Exit For
    Else
    End If
Next
MsgBox lvRow

End Sub

 

[YasserKhalil]

Great idea Mr. Chihiro to loop from last row till find the target and desired row
In fact I thought the same idea but I need a shortcut way to achieve that ..Is there an easy and short way to find the last row with non-zero values?

 

[bosco_yip]

To obtain the last numeric value exclude 0,

it is quite simple in formula way :

=1/LOOKUP(9.99E+307,1/B:B)

Regards
Bosco

 

[Chihiro]

Yep, I was going to suggest sticking below formula in one of the cell too.
=LOOKUP(2,1/(B:B>0),ROW(B:B))

 

[bosco_yip]

Yep, I was going to suggest sticking below formula in one of the cell too.
=LOOKUP(2,1/(B:B>0),ROW(B:B))

But this formula inclusive both Numeric and Text value
.
Regards

 

[bosco_yip]

Row number of last numeric value exclude 0

=-LOOKUP(1,-ROW(B:B)/ISNUMBER(B:B)/(B:B>0))

Regards

 

[YasserKhalil]

Thanks for formulas but I need it using vba code
Thank you very much for your contributions

 

[jindon]

    With Range("b1", Range("b" & Rows.Count).End(xlUp))
        MsgBox Evaluate("max(if((" & .Address & "<>"""")*(" & .Address & "<>0),row(" & .Address & ")))")
    End With

 

[YasserKhalil]

Thank you very much Mr. Jindon
That's exactly what I need .. simple and great code
Thanks for sharing

 

[jindon]

OOps, should be

Sub x()
    With Range("b1", Range("b" & Rows.Count).End(xlUp))
        MsgBox Evaluate("max(if((isnumber(" & .Address & "))*(" & .Address & "<>"""")*(" & .Address & "<>0),row(" & .Address & ")))")
    End With
End Sub

 

[YasserKhalil]

Mr. Jindon the first was working well with me .. What about the change in your last post .. as both are working ..
Which is better ?

 

[jindon]

You will see, if you add any String value such like "x" after the data in col.A.

 

[YasserKhalil]

Yes got your point now .. But I Liked first posted as it deals with strings aslo (both numbers and strings) .because in my file sometimes there are string results