Count remaining rows after autofilter has been applied

  • Thread starter Thread starter walshy06
  • Start date Start date
W

walshy06

Hi,

I need to be able to count the number of cells that I highlight with
the cursor in a column.

For Example: I have a spreadsheet with 1000 records.

I will need to run autofilter to retrict my rows.

Then I need to be able to count the number of rows that are *remaining
*after the autofilter has been applied.

If I could highlight the remaining rows with the curser and then run a
macro to count the number of rows that I have highlighted?? Any
thoughts?

This is urgent so any help would be great!

Thanks!
 
Dim rng as Range
set rng = ActiveSheet.Autofilter.Range.Columns(1).specialCells(xlVisible)

msgbox rng.Count - 1 & " data rows are visible"
 
Thanks for your help,


I'm getting a compile error when I run the code?


Sub count_rows()

Dim rng As Range

set rng = sheet1.Autofilter.Range.Columns(1).specialCel ls(xlVisible)

MsgBox rng.Count - 1 & " data rows are visible"

End Sub



Any idea what changes I need to make?

Thanks again
 
Looks like you misspelled "specialcells"

There is no space between the "L's" in "cells"

I copied the code from my original post, put it in a module (with a sub) and
ran it on a sheet with an autofilter applied and it ran like a champ.

That said, sometimes when you copy stuff from Google, it inserts stray
characters - so maybe you were a victim of that.
 
Tom, I am trying to run this code in Excel 2k and I get a run-time error 91
"Object variable or With block variable not set". With so little code, why is
this not working? Thanks in advance for your assistance. GDCROSS
 
You haven't applied data|filter|autofilter to your worksheet -- look for those
dropdown arrows and you'll see that they aren't there.
 
Back
Top