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

Dim rng as Range
set rng = ActiveSheet.Autofilter.Range.Columns(1).specialCel ls(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

--
walshy0
-----------------------------------------------------------------------
walshy06's Profile: http://www.excelforum.com/member.php...fo&userid=3413
View this thread: http://www.excelforum.com/showthread.php?threadid=53901

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" wrote in
message ...

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!

--
walshy0
-----------------------------------------------------------------------
walshy06's Profile: http://www.excelforum.com/member.php...fo&userid=3413
View this thread: http://www.excelforum.com/showthread.php?threadid=53901

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).specialCel ls(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).specialCel ls(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