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

hrh_dash 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))")

vba to find last row with value not equals to 0

HansVogelaar
MVP
Aug 05, 2022
hrh_dash

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))")
- hrh_dash
  Iron Contributor
  Aug 05, 2022
  hi HansVogelaar , the code works but rows varies from workbook to workbook, therefore is it possible to have the code in dynamic?
  
  appreciate the assistance!
  - HansVogelaar
    MVP
    Aug 05, 2022
    hrh_dash
    
    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.

NikolinoDE

Gold Contributor

Aug 05, 2022

hrh_dash

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)

hrh_dash
Iron Contributor
Aug 05, 2022
appreciate the help anyways!

Forum Discussion

vba to find last row with value not equals to 0

Share

Resources