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