• Status: Solved
• Priority: Medium
• Security: Public
• Views: 455

# Excel 2010 - find last non-0 cell in column

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.

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).

Regards,
Alexis
0
alexisbr
• 3
1 Solution

Older than dirtCommented:
Put this function in a module and then

=FollowedBy5Zeros()

``````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
Next

End Function
``````
0

Older than dirtCommented:
I edited the above.
0

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

Alexis
0

Older than dirtCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.