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

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

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

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

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

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

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

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

Look for value in a column and delete
 

Sorry, thats columns V to AB

Tempy

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

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

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

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