Filter & clear cells

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

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

--
Don Guillett
SalesAid Software
donaldb@281.com
"Tempy" <anonymous@devdex.com> wrote in message
news:uA8LnFUXFHA.1040@TK2MSFTNGP10.phx.gbl...
> 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 ***

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.

--
Regards,
Tom Ogilvy


"Tempy" <anonymous@devdex.com> wrote in message
news:uA8LnFUXFHA.1040@TK2MSFTNGP10.phx.gbl...
> 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 ***

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" <anonymous@devdex.com> wrote in message
news:uA8LnFUXFHA.1040@TK2MSFTNGP10.phx.gbl...
> 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 ***

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

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

--
Regards,
Tom Ogilvy


"Tempy" <anonymous@devdex.com> wrote in message
news:uwXlwzcXFHA.3488@tk2msftngp13.phx.gbl...
> 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 ***

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