Count remaining rows after autofilter has been applied

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"

--
Regards,
Tom Ogilvy


"walshy06" wrote:

>
> 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!
>
>
> --
> walshy06
> ------------------------------------------------------------------------
> walshy06's Profile: http://www.excelforum.com/member.php...o&userid=34132
> View this thread: http://www.excelforum.com/showthread...hreadid=539012
>
>

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.

--
Regards,
Tom Ogilvy


"walshy06" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> walshy06
> ------------------------------------------------------------------------
> walshy06's Profile:
http://www.excelforum.com/member.php...o&userid=34132
> View this thread: http://www.excelforum.com/showthread...hreadid=539012
>

Thanks for your help, That works perfectly!!

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

"Tom Ogilvy" wrote:

> Dim rng as Range
> set rng = ActiveSheet.Autofilter.Range.Columns(1).specialCells(xlVisible)
>
> msgbox rng.Count - 1 & " data rows are visible"
>
> --
> Regards,
> Tom Ogilvy
>
>
> "walshy06" wrote:
>
> >
> > 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!
> >
> >
> > --
> > walshy06
> > ------------------------------------------------------------------------
> > walshy06's Profile: http://www.excelforum.com/member.php...o&userid=34132
> > View this thread: http://www.excelforum.com/showthread...hreadid=539012
> >
> >

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:
>
> 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
>
> "Tom Ogilvy" wrote:
>
> > Dim rng as Range
> > set rng = ActiveSheet.Autofilter.Range.Columns(1).specialCells(xlVisible)
> >
> > msgbox rng.Count - 1 & " data rows are visible"
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "walshy06" wrote:
> >
> > >
> > > 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!
> > >
> > >
> > > --
> > > walshy06
> > > ------------------------------------------------------------------------
> > > walshy06's Profile: http://www.excelforum.com/member.php...o&userid=34132
> > > View this thread: http://www.excelforum.com/showthread...hreadid=539012
> > >
> > >

--

Dave Peterson