Further help on delete criteria found

[Tempy]

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 ***

 

[Tom Ogilvy]

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

 

[Tempy]

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 ***

 

[Tom Ogilvy]

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.

 

[Tempy]

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 ***

 

[Bob Phillips]

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.

 

[Tempy]

Hi all,
I apologise for the delay, time difference, below is an example; I need
to sort by #N/A which was a formula, but copyied and then pasted as
special-values only. I then need to clear the cells v to AB ONLY as all
the other cells in the same row have data that i require

V W X Y Z AA AB

2163254 11 01 18-Apr-05 J N
#N/A 11 #N/A 21-Jan-04
2078426 11 01 18-Mar-05 J J
2078427 11 01 10-Jan-05 J J
2077697 11 01 03-Mar-05 J J
2028870 11 01 23-Mar-05 J J
1933445 11 01 14-Dec-04 J J

Hopefully this will help

Tempy

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