Excel 2010 - find last non-0 cell in column

Posted on 2012-08-28
Last Modified: 2012-08-28
Hi.  I wrote a process in Excel to load data and show graphs that has been running for over a year.  I realized when it didn't work yesterday that I didn't handle if there is a bad data value, which rarely happens but it could happen again.  

The current formula in the cell, which is below, works if there are no data issues in the text file.

I am trying to figure out how to change the formula to look at the J column and return the cell only if that cell is followed by at least 5 cells with 0.00 in them.

I've included an image of how the data looks in column J.  Normally the value in J2 is a non-zero value.
J column data

My old formula would return J1, based on this data, but the new formula or function should return J7 (because 5 cells follow that have 0.00).

Thanks for any help you can offer.

Question by:alexisbr
    LVL 44

    Accepted Solution

    Put this function in a module and then


    Function FollowedBy5Zeros() As String
        Dim lngLastRow As Long
        Dim lngIndex As Long
        lngLastRow = Range("J65536").End(xlUp).Row
        For lngIndex = 1 To lngLastRow
            If Range("J" & lngIndex + 1) = 0 And _
               Range("J" & lngIndex + 2) = 0 And _
               Range("J" & lngIndex + 3) = 0 And _
               Range("J" & lngIndex + 4) = 0 And _
               Range("J" & lngIndex + 5) = 0 Then
               FollowedBy5Zeros = "J" & lngIndex
               Exit Function
            End If
    End Function

    Open in new window

    LVL 44

    Expert Comment

    by:Martin Liss
    I edited the above.

    Author Comment

    Thank you very much.  That works perfectly.  I understand what you wrote but I wouldn't have thought to do it that way.  

    LVL 44

    Expert Comment

    by:Martin Liss
    You're welcome and I'm glad I was able to help. My MVP is for VB6 and so I always think about code:)

    Marty - MVP 2009 to 2012

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now