Delete row if formula brings back an error

[keri]

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.

 

[ankur]

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

 

[keri]

Thankyou for this and putting me on the right track.