Filter & clear cells

[Tempy]

Hi All,
I am still siting whith a problem with the following:

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

It worked perfectly well until i have a blank row in between the cells
with "#N/A" in, it filters them all but it does not clear the cells
after the blank row ?

Please can someone help with this.

Tempy

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

 

[Don Guillett]

try this idea
Sub clearNA()
x = Range("b2")
With Range("B2:B10")
..AutoFilter Field:=1, Criteria1:="#N/A"
..ClearContents
End With
Range("b2") = x
End Sub

 

[Tom Ogilvy]

If the only errors showing in column V are #N/A, then you can try:

On Error Resume Next
columns(22).Specialcells(xlConstants,xlErrors).ClearContents
columns(22).Specialcells(xlFormulas,xlErrors).ClearContents
On Error goto 0

Not sure what you are doing with the currentregion, but if you describe what
you are doing, there may be an extension of this that will work.

 

[Bernie Deitrick]

Tempy,

To account for the blank row, you need to do it without using the
..Currentregion (and Selection isn't needed, usually).

What is the currentregion around V5? Do you have blank columns anywhere, or
would that statement normally pick up the whole table? What range would
normally be cleared if you didn't have the blank row(s)?

HTH,
Bernie
MS Excel MVP

 

[Tempy]

Good morning (South Africa) and evening USA,

Thanks for the replies, the #N/A is a result of a Vlookup and will
always be in Column "V". I then copy the complete column & past the
value to get rid of the formula. It is then that i run the code to get
rid of the #N/A but where i have an instance of #N/A i must also clear
the cells from V:AB ONLY, as the others cells have data in.


Tempy

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

 

[Tom Ogilvy]

On Error Resume Next
set rng = columns(22).Specialcells(xlConstants,xlErrors)
On Error goto 0
If not rng is nothing then
Intersect(Range("V:AB"),rng.EntireRow).ClearContents
End If

 

[Tempy]

Thank you Tom, this does exactly what i wanted. Tom, could one use this
formula but searching for a value and not an error eg. a 1 or 0 ?
If yes how would one change the code ?

Tempy

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