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