Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for value in a column and delete
Good day,
I need to look down a column for a certain value and delete all occurances. I have done this by filtering using the code below but not all instances are deleted ? Sub Test() ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2 Selection.AutoFilter Field:=22, Criteria1:="#N/A" Cells(Range("V5").CurrentRegion.Offset(6, 0).SpecialCells(xlCellTypeVisible).Row, 22).Select Selection.CurrentRegion.Select Selection.ClearContents Range("V311:Z311").Clear test2 End Sub Could somebody help with some better code or suggestion. Thanks in advance Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for value in a column and delete
What happens in your test, and why 22 in the autofilter?
-- HTH RP (remove nothere from the email address if mailing direct) "Tempy" wrote in message ... Good day, I need to look down a column for a certain value and delete all occurances. I have done this by filtering using the code below but not all instances are deleted ? Sub Test() ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2 Selection.AutoFilter Field:=22, Criteria1:="#N/A" Cells(Range("V5").CurrentRegion.Offset(6, 0).SpecialCells(xlCellTypeVisible).Row, 22).Select Selection.CurrentRegion.Select Selection.ClearContents Range("V311:Z311").Clear test2 End Sub Could somebody help with some better code or suggestion. Thanks in advance Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for value in a column and delete
Hi Bob,
the 22 is the number of the auto filter field to filter. As stated previously not all the filtered criteria is deleted. Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for value in a column and delete
Hi all, maybe i did not explain my problem properly; I have 151 columns
and each one can be filtered. There are between 10 and 5000 lines. I need to look for #N/A in column "V:AB" starting at row 6 and once found then i then need to delete all instances. This could be scattered down the rows, however if row 10 has #N/A in column V then the other columns will also have it in but i cannot delete the row but must delete the contents of the cells. Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for value in a column and delete
This works for me
Sub FilterData() Dim iLastRow As Long Dim i As Long aplication.ScreenUpdating = False For i = 22 To 27 'V to AB iLastRow = Cells(Rows.Count, i).End(xlUp).row With Range(Cells(5, i), Cells(iLastRow, i)) If iLastRow = 6 Then .AutoFilter Field:=1, Criteria1:="#N/A" .SpecialCells(xlCellTypeVisible).ClearContents End If End With Next i Application.ScreenUpdating = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Tempy" wrote in message ... Hi all, maybe i did not explain my problem properly; I have 151 columns and each one can be filtered. There are between 10 and 5000 lines. I need to look for #N/A in column "V:AB" starting at row 6 and once found then i then need to delete all instances. This could be scattered down the rows, however if row 10 has #N/A in column V then the other columns will also have it in but i cannot delete the row but must delete the contents of the cells. Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for value in a column and delete
That of course should be
Application.ScreenUpdating = False -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... This works for me Sub FilterData() Dim iLastRow As Long Dim i As Long aplication.ScreenUpdating = False For i = 22 To 27 'V to AB iLastRow = Cells(Rows.Count, i).End(xlUp).row With Range(Cells(5, i), Cells(iLastRow, i)) If iLastRow = 6 Then .AutoFilter Field:=1, Criteria1:="#N/A" .SpecialCells(xlCellTypeVisible).ClearContents End If End With Next i Application.ScreenUpdating = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Tempy" wrote in message ... Hi all, maybe i did not explain my problem properly; I have 151 columns and each one can be filtered. There are between 10 and 5000 lines. I need to look for #N/A in column "V:AB" starting at row 6 and once found then i then need to delete all instances. This could be scattered down the rows, however if row 10 has #N/A in column V then the other columns will also have it in but i cannot delete the row but must delete the contents of the cells. Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for value in a column and delete
Hi Bob,
That works ok, but only clears column 22 and i need it to clear columns 22 to 28. Thanks, Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for value in a column and delete
My filter will be column 22 and then it must only clear the filtered
cells in columns V to VB only. Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for value in a column and delete
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for value in a column and delete
Do you mean that you determine the #N/A in column V, then clear V-AB,
regardless of whether those other columns have a #N/A in that row? The code I gave goes through each column one at a time. -- HTH RP (remove nothere from the email address if mailing direct) "Tempy" wrote in message ... Sorry, thats columns V to AB Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for value in a column and delete
Hi Bob,
Thanks yes that is correct, if column V has #N/A in it then i must clear cells V - AB in that row. In other words if the filter 22 filters for #N/A all exposed cells in V-AB only must be cleared. All other cells in other columns must not be affected. Thanks for the help Bob. Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for value in a column and delete
You do realise that by filtering on column V, those rows will be exposed for
al columns V-AB? That seems to conflict with your statement '... all exposed cells in V-AB only must be cleared ...' to me. -- HTH RP (remove nothere from the email address if mailing direct) "Tempy" wrote in message ... Hi Bob, Thanks yes that is correct, if column V has #N/A in it then i must clear cells V - AB in that row. In other words if the filter 22 filters for #N/A all exposed cells in V-AB only must be cleared. All other cells in other columns must not be affected. Thanks for the help Bob. Tempy *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy column header to next column, delete & delete every nth colum | New Users to Excel | |||
delete cells column. Delete empty cells | Excel Worksheet Functions | |||
How do I delete everything after a / in a column? | Excel Worksheet Functions | |||
Delete column if .... | Excel Programming | |||
Delete All Rows That Column A value is not in Column A of Sheet2 | Excel Programming |