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 with
regard to autofiltered ranges. I'm trying to reference a couple
subsets of my whole table and change a field in one subset depending on
the number of rows in the other subset. Something like the following
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 to
copy the autofiltered rows to another worksheet. However, when I try
to .count my autofiltered rows I get the count of the whole table. I'd
also really like to avoid copying, and just manipulate the data in place
referenced by an autofilter range.

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

Here's the actual code I used to implement my autofilter and
(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,
Cyle


--
c-monster
------------------------------------------------------------------------
c-monster's Profile: http://www.excelforum.com/member.php...o&userid=37843
View this thread: http://www.excelforum.com/showthread...hreadid=573976