SOLVED

# vba to find last row with value not equals to 0

I would like to have a code to find last row that does not equals to 0. Based on the screenshot, using the debug.Print, it should print row 34 for column Q. thanks and appreciate the assistance in advance

6 Replies

# Re: vba to find last row with value not equals to 0

``````Sub LastRowNotEqualZero()
Dim LastCell As Long
Dim i As Long
LastCell = Cells(Rows.Count, 1).End(xlUp).Row
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 1) <> "0" Then 'here then like formula

next i
end sub``````

hope i could help :)

NikolinoDE

I know I don't know anything (Socrates)

best response confirmed by hrh_dash (Iron Contributor)
Solution

# Re: vba to find last row with value not equals to 0

Alternatively, let Excel figure it out by evaluating a formula:

LastRowNonZero = [MAX((Q2:Q1000<>0)*ROW(Q2:Q1000))]

or

LastRowNonZero = Evaluate("MAX((Q2:Q1000<>0)*ROW(Q2:Q1000))")

# Re: vba to find last row with value not equals to 0

hi @Hans Vogelaar , the code works but rows varies from workbook to workbook, therefore is it possible to have the code in dynamic?

appreciate the assistance!

# Re: vba to find last row with value not equals to 0

appreciate the help anyways!

# Re: vba to find last row with value not equals to 0

What is the highest row number of a cell with a non-zero value that you expect in any workbook? If it will never be more than 1000, the code that I posted will be sufficient. But if it could be up to - say - 15000, simply change 1000 to 15000 in the code. It doesn't matter if 15000 is too high for most of the workbooks.

# Re: vba to find last row with value not equals to 0

that is true as well, i dont think the data would could go up to more than 5000. thanks for the assistance!