why can't 'Do While Each Cell in Range...'?

[Ian Elliott]

Thanks in advance.
I have some code that finds the first non-zero cell in a
row:
Dim cell as Range
RowNumber=1
For Each cell In Range("D1:" & ActiveSheet.Range
("D65536").End(xlUp).Address)
If cell = 0 then
RowNumber = RowNumber + 1 'increase by one row
Else
Exit For
End If
Next
But I figure I could get this down a line or two by:
Do While Each cell in Range("D1:" & ActiveSheet.Range
("D65536").End(xlUp).Address) <> 0
RowNumber=RowNumber+1
Loop
But I get a syntax error (the line goes red) when I type
in the above Do While...
Is the 'each' code useable in Do Whiles?
If not, is there something else I can do?
Thanks again!

 

[EdgeOfCity]

For Each cell In Range("D1:" &
ActiveSheet.Range("D65536").End(xlUp).Address)
If cell <> 0 Then RowNumber = RowNumber + 1
Next
or try this:


lstRow = Range("d65536").End(xlUp).row
Set rng = Range("d1:d" & lstRow)
n1 = worksheetfunction.CountA(rng)
msgbox n1

 

[Bernie Deitrick]

Ian,

You can't use that structure, but you could use:

RowNumber = Application.CountIf(Range("D1",
Range("D65536").End(xlUp)), 0) +1

HTH,
Bernie
MS Excel MVP