Delete row if formula brings back an error
 

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.

Delete row if formula brings back an error
 

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 wrote:

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.

Delete row if formula brings back an error
 

Thankyou for this and putting me on the right track.


ankur wrote:
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 wrote:

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.