Hi,

Bob helped me with some code to find and delete criteria found but i
need further help.
I have 151 columns and 10 to 5000 rows.
In filter 22, column V, i must find all the cells with #N/A in and clear
all cells in columns V to AB.

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

I assume you mean clear their contents rather than delete. I will assume
#N/A is produced by a formula and you have no other error values in that
column

Sub ClearErrors()
Dim rng as Range, rng1 as Range, rng2 as range
On Error Resume Next
set rng = Columns(22).SpecialCells(xlformulas,xlErrors)
On Error goto 0
set rng1 = Range("V:AB")
if not rng is nothing then
set rng2 = Intersect(rng.entireRow,rng1)
rng2.clearContents
End if
End Sub

--
regards,
Tom Ogilvy



"Tempy" wrote in message
...
Hi,

Bob helped me with some code to find and delete criteria found but i
need further help.
I have 151 columns and 10 to 5000 rows.
In filter 22, column V, i must find all the cells with #N/A in and clear
all cells in columns V to AB.

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Hi Thom,

sorry i do not think i am explaining myself clearly; Column V has the
error #N/A in, column W has a number, column X has #N/A and Y has a date
in. If V has the error #N/A then i must clear V, W, X and Y.

Hopefully that is clearer

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Maybe you should try it. If works base on the assumptions I stated. Also,
you said you wanted to clear V to AB.

If you actually want to delete those rows, then post back.

--
Regards,
Tom Ogilvy


"Tempy" wrote in message
...
Hi Thom,

sorry i do not think i am explaining myself clearly; Column V has the
error #N/A in, column W has a number, column X has #N/A and Y has a date
in. If V has the error #N/A then i must clear V, W, X and Y.

Hopefully that is clearer

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Hi Thom,

I tried your code without success, can i try & explain the problem
again.

I have to filter column 22 (V) for #N/A, which was an error code, but
the formular is removed and just the value saved. I then need to clear
all the cells from V:AB.

The columns have different values in as below

v W X Y Z AA AB
#N/A 11 #N/A 21-Jan-02 02 N or J N or J

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Post back with a number of sample lines, before and what you want them to
look like after. We are having trouble understanding what you want.

--
HTH

Bob Phillips

"Tempy" wrote in message
...
Hi Thom,

I tried your code without success, can i try & explain the problem
again.

I have to filter column 22 (V) for #N/A, which was an error code, but
the formular is removed and just the value saved. I then need to clear
all the cells from V:AB.

The columns have different values in as below

v W X Y Z AA AB
#N/A 11 #N/A 21-Jan-02 02 N or J N or J

Tempy

*** Sent via Developersdex http://www.developersdex.com ***