Stop Do While at Null

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 23 Mar 2006 22:20 GMT

What is the function to end a Do While statemnet when encountering a null,
blank or empty cell?

Thank you
Dave

Reply to this Message

Chip Pearson - 23 Mar 2006 22:23 GMT

Dave,

Try

If IsEmpty(ActiveCell) Then
Exit Do
End If

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

> What is the function to end a Do While statemnet when
> encountering a null,
> blank or empty cell?
>
> Thank you
> Dave

Reply to this Message

Zack Barresse - 23 Mar 2006 22:24 GMT

Hi Dave,

Something like this...

Do Until rngYourCell.Value = ""
'loop activity here
Loop

Signature

Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM

> What is the function to end a Do While statemnet when encountering a null,
> blank or empty cell?
>
> Thank you
> Dave

Reply to this Message

Tom Ogilvy - 23 Mar 2006 22:40 GMT

i = 1
do while not isempty(cells(i,1))
cells(i,1).interior.colorIndex = 3
i = i + 1
Loop

i = 1
Do
cells(i,1).Interior.ColorIndex = 3
i = i + 1
Loop until isempty(cells(i,1)

Signature

Regards,
Tom Ogilvy

> What is the function to end a Do While statemnet when encountering a null,
> blank or empty cell?
>
> Thank you
> Dave

Reply to this Message

Dave - 23 Mar 2006 22:49 GMT

Should this do it? "Loop Until IsEmpty(ActiveCell.Cells)"

> i = 1
> do while not isempty(cells(i,1))
[quoted text clipped - 13 lines]
> > Thank you
> > Dave

Reply to this Message

Zack Barresse - 23 Mar 2006 23:29 GMT

Take out the ".Cells" portion and it should work.

Signature

Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM

> Should this do it? "Loop Until IsEmpty(ActiveCell.Cells)"
>
[quoted text clipped - 16 lines]
>> > Thank you
>> > Dave

Reply to this Message

Tom Ogilvy - 24 Mar 2006 02:03 GMT

You can use that, but in this case it superfluous. Better to do as Zack
advises

as demonstrated from the immediate window:
? isempty(activecell.Cells)
True
? isempty(activecell)
True

Eventually you will mature in your coding want to stop selecting cells,
which slows your code way down and makes the screen flash. When you do, use
the methods I originally suggested.

Signature

Regards,
Tom Ogilvy

> Should this do it? "Loop Until IsEmpty(ActiveCell.Cells)"
>
[quoted text clipped - 15 lines]
> > > Thank you
> > > Dave

Reply to this Message