Your interpretation of what Item(2) is not entirely correct either. The code
I posted worked fine, but only because I used a single column AutoFilter for
my testing. The number in the parentheses is does not refer to the column,
per se; rather, it refers to the second cell in the range with the order of
counting the cells running across the row first before dropping down to the
next lower row, and then across that row until its end before dropping down
to the row below it, and so on. So, in a single column range, Item(2) does,
in fact, refer to the second cell down because there is no second column
available in the first row for a cell there to be counted. If you have
multiple columns, then Item(2) would, in that case, as you pointed out,
refer to the first cell in the second column. Of course, that means my using
2 as a constant was a bad idea because I simply didn't think about two or
more columned AutoFilter when developing my code. A bad mistake on my part,
so thanks for catching it. However, I am glad I made that mistake so that
you could bring it to my attention. Why? Because in re-examining the
problem, I ended up realizing the required code is much simpler than I first
imagined it to be...the code for the main functionality turns out, in the
end, to basically be a one-liner.

Sub CountVisibleRows()
Dim R As Range, C As Long, OmitHeaderFromCount As Boolean
OmitHeaderFromCount = True
C = Worksheets("Sheet2").AutoFilter.Range.Columns(1). _
SpecialCells(xlVisible).Count + OmitHeaderFromCount
MsgBox "There are " & C & " visible AutoFilter'ed rows."
End Sub

Note that I added a Boolean variable named OmitHeaderFromCount to let the
programmer be able to set whether the header should be omitted from the
count or not. In my example above, I chose to omit it from the count by
setting the variable to True.

--
Rick (MVP - Excel)


"OssieMac" wrote in message
...
Hi Rick,

I always like seeing alternative methods. However, your code needed a
little
tweeking.

I believe you have the wrong interpretation of Item(2). Item(2) refers to
the 2nd column and your code counts all visible cells, including column
headers, to the end of the autofiltered range.

Changing the 2 to Columns.Count will count all visible cells from the
start
of the last column to the end of the autofiltered range and includes
column
headers. This is demonstrated in the following example that displays the
ranges being counted as well as the row count.

Sub CountVisibleRows2()

Dim R As Range, C As Long
With Worksheets("Sheet1").AutoFilter.Range
MsgBox Range(.Item(.Columns.Count), _
.Item(.Count)).SpecialCells(xlVisible).Address

C = Range(.Item(.Columns.Count), _
.Item(.Count)).SpecialCells(xlVisible).Count
End With

'Subtract the column header
MsgBox "There are " & C - 1 & " visible AutoFilter'ed rows."

End Sub


--
Regards,

OssieMac