[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Delete row if formula brings back an error

keri

12/14/2006 11:13:00 AM

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.
2 Answers

an01digital

12/14/2006 11:22:00 AM

0


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.

keri

12/14/2006 11:45:00 AM

0

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.