Look for value in a column and delete

T

Tempy

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

Bob Phillips

What happens in your test, and why 22 in the autofilter?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
T

Tempy

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

Bob Phillips

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)
 
B

Bob Phillips

That of course should be

Application.ScreenUpdating = False


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

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)
 
T

Tempy

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

Bob Phillips

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top