vba - working with autofiltered ranges

[c-monster]

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]

This post got buried under 8 pages of other posts on the first day. Hopefully the right person hasn't seen it yet.

Maybe autofilter is the wrong way to do this. I want to do the excel-vba equivalent of an sql query, and then operate on that resulting dataset (i.e. looping through, counting rows, etc.)

Anyone have any ideas?

Thanks,
Cyle

[mudraker]

Cyle

To count rows try this

Sub CountRows()
Dim lTotal As Long
Dim lLastRow As Long

lLastRow = Cells(Rows.Count, "a").End(xlUp).Row
lTotal = Application.WorksheetFunction.Subtotal(3, Range("A2:A" & lLastRow))
MsgBox lTotal
End Sub

Note:- The SUBTOTAL function only works for AutoFiltering and outlining. If you hide rows manually, it won't return the correct result.

Changing the Subtotal(3 to another number changes what gets counted