Count displayed rows

[GDCross]

I am trying to run this code in Excel 2k so that I can count the number of
displayed rows when I display only Subtotal totals. Unfortunately, I get a
run-time error 91
"Object variable or With block variable not set". With so little code, I can
believe this is not working? Desperately seeking wisdom :{

Dim rng as Range
set rng = ActiveSheet.Autofilter.Range.Columns(1).specialCells(xlVisible)
msgbox rng.Count - 1 & " data rows are visible"

[Dave Peterson]

Same response as Tuesday:


You haven't applied data|filter|autofilter to your worksheet -- look for those
dropdown arrows and you'll see that they aren't there.



GDCross wrote:
>
> I am trying to run this code in Excel 2k so that I can count the number of
> displayed rows when I display only Subtotal totals. Unfortunately, I get a
> run-time error 91
> "Object variable or With block variable not set". With so little code, I can
> believe this is not working? Desperately seeking wisdom :{
>
> Dim rng as Range
> set rng = ActiveSheet.Autofilter.Range.Columns(1).specialCells(xlVisible)
> msgbox rng.Count - 1 & " data rows are visible"

--

Dave Peterson