Count displayed rows
 

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).specialCel ls(xlVisible)
msgbox rng.Count - 1 & " data rows are visible"

Count displayed rows
 

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).specialCel ls(xlVisible)
msgbox rng.Count - 1 & " data rows are visible"

--

Dave Peterson