vba - working with autofiltered ranges
 

Hi all,
I have a data table with the typical address book format:
ID name phone address state region color score ...

I'm having trouble getting my head around the Excel object model wit
regard to autofiltered ranges. I'm trying to reference a coupl
subsets of my whole table and change a field in one subset depending o
the number of rows in the other subset. Something like the followin
pseudo code to give you the idea of the process....

for each region in ListOfRegions
group1 = autofilter(myTable, region=south)
group2 = autofilter(myTable, color=blue)
for each row in group2
if group2.cell(row,score) group1.col(score).max then
group2.cell(row,region) = south
next row
next region

I have been able to implement autofilter and use the .copy method t
copy the autofiltered rows to another worksheet. However, when I tr
to .count my autofiltered rows I get the count of the whole table. I'
also really like to avoid copying, and just manipulate the data in plac
referenced by an autofilter range.

Could someone please help me understand how this works, and what synta
I should use?

Here's the actual code I used to implement my autofilter an
(incorrect).count
With Sheets("Staff")
.AutoFilterMode = False
.Range("a1:m1").AutoFilter
.Range("a1:m1").AutoFilter field:=13, Criteria1:="south"
Set assigned = .AutoFilter.Range
assigned.Copy Destination:=Sheets("scratch").Range("a1")
MsgBox "number: " & assigned.Rows.Count, vbOKOnly
End With

Thanks,
Cyl

--
c-monste
-----------------------------------------------------------------------
c-monster's Profile: http://www.excelforum.com/member.php...fo&userid=3784
View this thread: http://www.excelforum.com/showthread.php?threadid=57397