Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further help on delete criteria found
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further help on delete criteria found
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further help on delete criteria found
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further help on delete criteria found
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further help on delete criteria found
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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further help on delete criteria found
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 *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further help on delete criteria found
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to delete a row if strong NOT found.... | Excel Discussion (Misc queries) | |||
Return all values found for criteria | Excel Discussion (Misc queries) | |||
How to delete values of a cell if it is found in another coloumn | Excel Worksheet Functions | |||
specialcells method errors when criteria not found | Excel Programming | |||
Go to record found by Criteria in Data Form | Excel Programming |