About Us

Nigel[_2_]

Many thanks for your help, I have got it now!

--

Regards,
Nigel

"Dave Peterson" wrote in message
...
somerange.rows.count
will return the number of rows in the first area--not the total number of
rows
in all the range.

For your autofilter.range, you'd want something like:

msgbox somesheet.autofilter.range.columns(1) _
.cells.specialcells(xlcelltypevisible).cells.count

Nigel wrote:

What I was asking was can I read thru the Range rather than just copy it
to
a new location. I have tried using VisRng.Cells(x,y), where x and y are
the
row and column in the range.

On another point, I tried the code you posted and I get inconsistent
results
depending on the filter setting.

Sheet 1 contains some test data as follows the highlighted row marked c1,
c2, c3 and c4 has the autofilter.
c1 c2 c3 c4
1 2 3 4
1 22 33 44
2 333 444 555
2 3333 4444 5555
1 33333 44444 55555

using the range setting .....
Set VisRng = .Offset(1, 0).Resize(.Rows.Count - 1)_
.Cells.SpecialCells(xlCellTypeVisible)

and testing the range rows count using

VisRng.Rows.Count

with no filter I get 5 as expected
with filter in column 1 set to '2' I get 2 as expected
with filter in column 1 set to '1' I get 2 - not expected there should be
three!

Do you know why?

--

Regards,
Nigel

"Dave Peterson" wrote in message
...
I don't understand your question.

The code I suggested (as well as the manual technique) can be used to
copy
to a
new location.

This is the portion that would do the copy:

if visrng is nothing then
'warning message???
else
visrng.copy _
destination:=worksheets("Somesheetnamehere").range ("a1")
end if

Nigel wrote:

Hi Dave
Thanks, I read this to mean I could copy the data (visible rows only)
to
a
new a range which I can then read into my reports.

In the VisRng just created there maybe several rows and columns, I
presume
use something like

Dim Cell as Range
For Each Cell in Visrng
Cell.Offset(0,0).Value = Row 1 / Column 1
Cell.Offset(0,1).Value = Row 1 / Column 2
Next

Is this correct?

--

Regards,
Nigel

"Dave Peterson" wrote in message
...
Manually, you can select that visible range and copy|paste to a new
location.
Excel (after xl95) will only copy the visible cells.

It's kind of like:
selecting the range
edit|goto|special|visible cells only
edit|copy
then paste

In code:

Dim HowManyVisRows as long
dim VisRng as range

With worksheets("somesheetname").autofilter.range
'subtract one for the header.
howmanyvisrows _
=
.columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1

if howmanyvisrows 0 then
'avoid the header and come down one row
set visrng = .resize(.rows.count-1).offset(1,0) _
.cells.specialcells(xlcelltypevisible)
else
set visrng = nothing
end if
end with

if visrng is nothing then
'warning message???
else
'do what you want
end if

Untested, uncompiled--watch for typos.

Nigel wrote:

Hi All
I use an autofilter on my 'database sheet' of records to
selectively
filter
the required data.

I currently scan the visible filtered list and transfer data to my
report
and chart sheets. I scan from the first row to the last filtered
and
visible row. Testing each row using Not EntireRow.Hidden. This
works
fine.

What concerns me is that as my 'database sheet' get longer, that
scanning
ALL rows, and selecting the not hidden rows is a big overhead and
things
will slow down.

IS there a better way? For example copying the filtered list to an
array
and scanning this - I am not sure how to do this.

--

Regards,
Nigel

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I count rows in a filtered list when using AutoFilter?	chiefcook	Excel Worksheet Functions	6	November 10th 08 09:21 AM
Scan column for first of a list	Chip	Excel Discussion (Misc queries)	1	May 24th 08 05:19 PM
Autofilter and count on filtered data	gr8posts	Excel Worksheet Functions	7	October 19th 07 09:17 PM
macro to scan list	Matto	Excel Programming	2	November 9th 04 05:19 PM
Selecting Filtered records only when AutoFilter is on	Owen Vickers	Excel Programming	4	February 21st 04 02:20 AM

Menu

About Us