Try this:

Option Explicit
Sub testme01()
Dim myRng As Range
Set myRng = ActiveSheet.Range("a1,a3").EntireRow
MsgBox myRng.Address & vbLf & myRng.Rows.Count

Set myRng = ActiveSheet.Range("a1:a5,a7").EntireRow
MsgBox myRng.Address & vbLf & myRng.Rows.Count
End Sub

You'll see that the .rows.count returns the number of rows in the first
area--not the number of rows in the entire range.

Instead, you can look at a single column in that autofiltered range and count
the number of visible cells.

If that number is 1, then only the headers are visible (no real data).

If you want the number of rows that are visible (not counting the header), just
subtract 1 from the quantity of visible cells in that column.

Dim vRng as range
dim vRows as long
with worksheets("Sheet999").autofilter.range
set vrng = .columns(1).cells.specialcells(xlcelltypevisible)
vrows = vrng.cells.count - 1
select case vrows
case is = 0 'shouldn't happen in your case!
case is = 1
'what should happen here
case is = 2
'what should happen here
case is 2
msgbox "oh, oh!"
end select
end with

(Untested, uncompiled. Watch for typos.)


bst wrote:

i'm working on an inventory tracking sheet that has a snapshot of data
for the current week and the week before. it tracks changes in inventory
assignment. i have already built an array that has each unique id for
the inventory that is being tracked. i then want to loop the array
applying an autofilter on the sheet with the criteria based on the array
data. the result of the autofilter (not counting the header) either 1
row (the item has been removed from inventory) or two rows (the data is
still in inventory, need to check if it has been reassigned).

my problem is that i can't seem to test for how many rows resulted in
the autofilter.

what i have so far:
for xCtr = 1 to totalSerials
ActiveSheet.UsedRange.Select
Selection.AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr)
Set filteredRange = selection.SpecialCells(xlCellTypeVisible)

if filteredRange.rows.count = 1 then
filteredRange.cells(2,colException).value = "x"
'row 2 because the first row is the header
end if
if filteredRange.rows.count = 2 then
if filteredRange.cells(2,colName).value < _
filteredRange.cells(3, colName).value then
filteredRange.cells(2, colException).value = "x"
filteredRange.cells(3, colException).value = "x"
end if
end if
selection.autofilter
next xCtr

then i'll sort the sheet based on the exception column

the first time the macro is ran it works fine, afterwards
filteredRange.rows.count returns 1 everytime. i get the same results
with filteredRange.specialcells(xlCellTypeVisible).coun t
i have tried assigning filteredRange different ways as well with the
same results.

do i even need filteredRange? if not how should i proceed? if so, how
can i get it to work as desired? i've read about 10 pages of google
searching autofilter in the newsgroup and can't seem to find what i
need. copying the rows to another sheet would make the macro run too
long and is not desired since i want to sort the results of the
conditionals above.

TIA
bst

--

Dave Peterson