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
(E-Mail Removed)

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
> (E-Mail Removed)

--

Dave Peterson

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
(E-Mail Removed)



"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>
> --
>
> Dave Peterson

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
> (E-Mail Removed)
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >> (E-Mail Removed)
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

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
(E-Mail Removed)



"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>> (E-Mail Removed)
>>
>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >> (E-Mail Removed)
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

Hi Dave
I have been investigating this issue and discover that the construct

With Sheets(1).AutoFilter.Range
Set VisRng = .Resize(.Rows.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With

Results in the selected range extent being as far down as the first hidden
row less the header. All rows that are not hidden after this are ignored!

True in both xl2003 and xl2007


--

Regards,
Nigel
(E-Mail Removed)



"Nigel" <nigel-(E-Mail Removed)> wrote in message
news:AEFB951D-5625-4695-A033-(E-Mail Removed)...
> 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
> (E-Mail Removed)
>
>
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>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
>>> (E-Mail Removed)
>>>
>>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>> > 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
>>> >> (E-Mail Removed)
>>> >
>>> > --
>>> >
>>> > Dave Peterson
>>
>> --
>>
>> Dave Peterson
>

The problem does not lye with the VisRng setting but the use of the function

VisRng.Rows.Count

This only shows the count for the first n rows that are not hidden.

Any ideas how I can access ALL rows in VisRng
--

Regards,
Nigel
(E-Mail Removed)



"Nigel" <nigel-(E-Mail Removed)> wrote in message
news:47C5E48C-85C0-42A2-9942-(E-Mail Removed)...
> Hi Dave
> I have been investigating this issue and discover that the construct
>
> With Sheets(1).AutoFilter.Range
> Set VisRng = .Resize(.Rows.Count - 1).Offset(1, 0) _
> .Cells.SpecialCells(xlCellTypeVisible)
> End With
>
> Results in the selected range extent being as far down as the first hidden
> row less the header. All rows that are not hidden after this are ignored!
>
> True in both xl2003 and xl2007
>
>
> --
>
> Regards,
> Nigel
> (E-Mail Removed)
>
>
>
> "Nigel" <nigel-(E-Mail Removed)> wrote in message
> news:AEFB951D-5625-4695-A033-(E-Mail Removed)...
>> 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
>> (E-Mail Removed)
>>
>>
>>
>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>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
>>>> (E-Mail Removed)
>>>>
>>>> "Dave Peterson" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>> > 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
>>>> >> (E-Mail Removed)
>>>> >
>>>> > --
>>>> >
>>>> > Dave Peterson
>>>
>>> --
>>>
>>> Dave Peterson
>>
>

If you want to loop through each of the visible rows, you could do this:

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

Notice that the .resize() portion has been changed to a single column.

Then you can loop through each of those cells in that range:

dim myCell as range
dim HowManyCols as long

With worksheets("somesheetname").autofilter.range
howmanycols = .columns.count
'subtract one for the header.
....

For each mycell in visrng.cells
'to copy that row
mycell.resize(1, howmanycolumns).copy
'to check the value of a different cell in that same row
if mycell.offset(0,5).value = 33 then
...
next mycell

I don't have a guess what happened with your filter. I'd try it again.

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
> (E-Mail Removed)
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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
> >> (E-Mail Removed)
> >>
> >> "Dave Peterson" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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
> >> >> (E-Mail Removed)
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Actually, that doesn't select anything.

If you added:
visrng.select

I would bet that it selects the visible rows in that range.

Nigel wrote:
>
> Hi Dave
> I have been investigating this issue and discover that the construct
>
> With Sheets(1).AutoFilter.Range
> Set VisRng = .Resize(.Rows.Count - 1).Offset(1, 0) _
> .Cells.SpecialCells(xlCellTypeVisible)
> End With
>
> Results in the selected range extent being as far down as the first hidden
> row less the header. All rows that are not hidden after this are ignored!
>
> True in both xl2003 and xl2007
>
> --
>
> Regards,
> Nigel
> (E-Mail Removed)
>
> "Nigel" <nigel-(E-Mail Removed)> wrote in message
> news:AEFB951D-5625-4695-A033-(E-Mail Removed)...
> > 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
> > (E-Mail Removed)
> >
> >
> >
> > "Dave Peterson" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >>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
> >>> (E-Mail Removed)
> >>>
> >>> "Dave Peterson" <(E-Mail Removed)> wrote in message
> >>> news:(E-Mail Removed)...
> >>> > 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
> >>> >> (E-Mail Removed)
> >>> >
> >>> > --
> >>> >
> >>> > Dave Peterson
> >>
> >> --
> >>
> >> Dave Peterson
> >

--

Dave Peterson

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
> (E-Mail Removed)
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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
> >> (E-Mail Removed)
> >>
> >> "Dave Peterson" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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
> >> >> (E-Mail Removed)
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson