Count remaining rows after autofilter has been applied

[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!

 

[Guest]

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

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

 

[walshy06]

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

 

[Tom Ogilvy]

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.

 

[walshy06]

Thanks for your help, That works perfectly!

 

[Guest]

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

 

[Dave Peterson]

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