Hello everybody!

Is there a possibiliy to find the last value of a list
that is not zero? Excel should search from the bottom of a
column and find the last entry < 0 ...

Thanks for your help,

Maria

This should do it....(assuming col a in sheet 1)

Sub FindNonZero()
Dim rngTarget As Range
Set rngTarget = Sheet1.Cells(65536, 1).End(xlUp)

While rngTarget.Value = 0
Set rngTarget = rngTarget.Offset(-1, 0)
Wend

MsgBox "the last non zero cell is " & rngTarget.Address
Set rngTarget = Nothing
End Sub

Hth,
OJ

Thanks you! Worked fine ...
Maria


-----Original Message-----
This should do it....(assuming col a in sheet 1)

Sub FindNonZero()
Dim rngTarget As Range
Set rngTarget = Sheet1.Cells(65536, 1).End(xlUp)

While rngTarget.Value = 0
Set rngTarget = rngTarget.Offset(-1, 0)
Wend

MsgBox "the last non zero cell is " & rngTarget.Address
Set rngTarget = Nothing
End Sub

Hth,
OJ

.

Bit heavy setting a range every time isn't it. Also, maybe wise tol check
for none

Sub FindNonZero()
Dim rngTarget As Range
Dim i As Long
Set rngTarget = Sheet1.Cells(Rows.Count, 1).End(xlUp)

With rngTarget
If .Row = 1 And .Value = 0 Then
MsgBox "There are no non-zero values in this column"
Exit Sub
Else
While .Offset(-i, 0).Value = 0
i = i + 1
Wend
MsgBox "the last non zero cell is " & rngTarget.Offset(-i,
0).Address
End If
End With
Set rngTarget = Nothing
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"OJ" wrote in message
ups.com...
This should do it....(assuming col a in sheet 1)

Sub FindNonZero()
Dim rngTarget As Range
Set rngTarget = Sheet1.Cells(65536, 1).End(xlUp)

While rngTarget.Value = 0
Set rngTarget = rngTarget.Offset(-1, 0)
Wend

MsgBox "the last non zero cell is " & rngTarget.Address
Set rngTarget = Nothing
End Sub

Hth,
OJ

fair point....

....taht said, just tried it on 65535 rows of 0's with the first non
zero in row 7 and it ran v quick. Does it take up that much
time/resource setting a range object? I dunno much about that side of
things...

OJ,

I did a quick rough and ready timed test, and it is about 8 times slower to
set a range object that to just increment a long variable (this was less
than I expected!).

It is true that in the actual data set it would still be quick, but these
things all add up, and in a complex app, you want to be as efficient as
possible.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"OJ" wrote in message
oups.com...
...taht said, just tried it on 65535 rows of 0's with the first non
zero in row 7 and it ran v quick. Does it take up that much
time/resource setting a range object? I dunno much about that side of
things...

food for thought indeed!! Cheers Bob. I have no formal
programming/testing education so I would be very curious to see how you
timed this...if you get a moment would you mind emailing me some sample
code so i can see your approach? Unlike almost everybody who uses these
list, my email address really is, well, my email address!!! (Much to
the delight of spammers and phishers...do people really fall for
those???)