Repeated application of AutoFilter
 

Hi,
I am attempting to apply an autofilter in a loop (to
compute subtotals). I have a single cell selected

Set ws = WorkSheet(1)
ws.Range("A1").Select
Selection.AutoFilter Field:=1, Criteria1:=FieldVal

FieldVal changes with every iteration of the loop.
I am attempting to get the special cells with

Set FilterData = ws.Range("A1")
MsgBox( FilterData.CurrentRegion.SpecialCells _
(xlVisible).Areas.Count)

The last statement gives me a value of 1 sometimes for
the second iteration and higher of my loop.

Unsure what I am doing wrong here.
Any help is much appreicated.
thanks,
-shishir

Repeated application of AutoFilter
 

If you have any hidden rows (which one would expect you would) then the
number of areas should be 1.
I assume you are setting the filter to show only one record. However, since
you are starting in row 1 and it is being treated as a header row (whether
it is or not - but it should be a header row), then row1 is always visible
and the matching record is visible -- unless that is row 2, then you will
get areas 1.

--
Regards,
Tom Ogilvy

sramam wrote in message
...
Hi,
I am attempting to apply an autofilter in a loop (to
compute subtotals). I have a single cell selected

Set ws = WorkSheet(1)
ws.Range("A1").Select
Selection.AutoFilter Field:=1, Criteria1:=FieldVal

FieldVal changes with every iteration of the loop.
I am attempting to get the special cells with

Set FilterData = ws.Range("A1")
MsgBox( FilterData.CurrentRegion.SpecialCells _
(xlVisible).Areas.Count)

The last statement gives me a value of 1 sometimes for
the second iteration and higher of my loop.

Unsure what I am doing wrong here.
Any help is much appreicated.
thanks,
-shishir