Delete row if formula brings back an error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

keri - 14 Dec 2006 11:12 GMT

Hi,

I have searched posts but can find nothing that covers this. I
originally thought the error brought back may be treated as a value,
and could delete the row based on this value but that doesn't seem to
work.

I have a formula operating in columns J & K on up to 26 sheets. When
the formula (in code) reaches the bottom of each sheet it may return
the error #VALUE! into J & K. This does not always happen.

What I would like to do is search for these #VALUE! cells and delete
the rows where they are found.

Reply to this Message

ankur - 14 Dec 2006 11:21 GMT

Hi Keri,

Try this.

Sub test()
For Each Sheet In ActiveWorkbook.Sheets
For Each cell In Sheet.Columns("J:K").Cells
If IsError(cell.Value) = True Then
Sheet.Rows(cell.Row).Delete (xlUp)
End If
Next cell

Next Sheet
End Sub

Regards
Ankur Kanchan
www.xlmacros.com

> Hi,
>
[quoted text clipped - 9 lines]
> What I would like to do is search for these #VALUE! cells and delete
> the rows where they are found.

Reply to this Message

keri - 14 Dec 2006 11:45 GMT

Thankyou for this and putting me on the right track.

> Hi Keri,
>
[quoted text clipped - 28 lines]
> > What I would like to do is search for these #VALUE! cells and delete
> > the rows where they are found.

Reply to this Message