Null Field Result in Loop
 

As part of a larger macro I am running the following loop.

For ILoop = NumRowsF850 To 1 Step -1
If Cells(ILoop, 5) = 0 Then
Rows(ILoop).Delete
End If
Next ILoop

When the loop gets to a cell that is null, it is evaluating the the second
line of code as true, i.e. the cell = 0.

I only want to branch to the "Then" part of the loop if the cell truly has a
0 in it, not if it is null.

How should the second line be written?

TIA.

Null Field Result in Loop
 

Then you need to test for that:

For ILoop = NumRowsF850 To 1 Step -1
If IsEmpty(Cells(ILoop, 5).Value) Then
If Cells(ILoop, 5).Value = 0 Then
Rows(ILoop).Delete
End If
End If
Next ILoop

On Tue, 19 Oct 2004 14:01:04 -0700, Ken wrote:

As part of a larger macro I am running the following loop.

For ILoop = NumRowsF850 To 1 Step -1
If Cells(ILoop, 5) = 0 Then
Rows(ILoop).Delete
End If
Next ILoop

When the loop gets to a cell that is null, it is evaluating the the second
line of code as true, i.e. the cell = 0.

I only want to branch to the "Then" part of the loop if the cell truly has a
0 in it, not if it is null.

How should the second line be written?

TIA.

Null Field Result in Loop
 

Hi Ken,

Ken wrote:
For ILoop = NumRowsF850 To 1 Step -1
If Cells(ILoop, 5) = 0 Then
Rows(ILoop).Delete
End If
Next ILoop

When the loop gets to a cell that is null, it is evaluating the the
second line of code as true, i.e. the cell = 0.

I only want to branch to the "Then" part of the loop if the cell
truly has a 0 in it, not if it is null.

How should the second line be written?

You can use the IsEmpty function to test if the cell contains anything:

If Not IsEmpty(Cells(lLoop, 5).Value) And Cells(lLoop, 5).Value=0 Then

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]