autofilter : how to go to next row ?
 

Hello,
I'm searching the correct syntax to go the the next cell(s) when autofilter
is active.
When I do activeCell.Offset(1,0).Select, it goes to a row which is not part
of the autofilter criterias.
Many thanks for your valuable help.

François

autofilter : how to go to next row ?
 

do
ActiveCell.Offset(1,0).Select
Loop until activecell.EntireRow.Hidden = False


--
Regards,
Tom Ogilvy


"François" wrote in message
...
Hello,
I'm searching the correct syntax to go the the next cell(s) when
autofilter
is active.
When I do activeCell.Offset(1,0).Select, it goes to a row which is not
part
of the autofilter criterias.
Many thanks for your valuable help.

François

autofilter : how to go to next row ?
 

Tom,

This response is elegant in its simplicity, but it seems to me that there
must be a better way. I have a lot that I want to do with filtered lists in
VBA so I'm real interested in this topic.

What I mean is this: when I'm on an Excel sheet that is AutoFiltered, I can
use the arrow keys to immediately move from one item in the filtered list to
the next item. As far as I can tell, there's no way to do this via VBA. The
method you mentioned in your post, for example, loops through every invisible
cell in between just to get to the next member of the filtered list. My
question is, isn't there a way to simply move from one visible cell to
another in VBA like I can in Excel?

My understanding is the one of VBA's disadvantages is speed, and that's why
I'm concerned with overtaxing the CPU unnecessarily.

An alternative I've come up with is to copy the filtered list to another
sheet and then step through it. Is this the best way?
"Tom Ogilvy" wrote:

do
ActiveCell.Offset(1,0).Select
Loop until activecell.EntireRow.Hidden = False


--
Regards,
Tom Ogilvy


"François" wrote in message
...
Hello,
I'm searching the correct syntax to go the the next cell(s) when
autofilter
is active.
When I do activeCell.Offset(1,0).Select, it goes to a row which is not
part
of the autofilter criterias.
Many thanks for your valuable help.

François

autofilter : how to go to next row ?
 

It depends on what you want to do. I can certainly get a reference to all
the visible rows.

Dim rng as Range, rng1 as Range, cell as Range
set rng = ActiveSheet.Autofilter.Range.Columns(1)
set rng = rng.offset(1,0).Resize(rng.rows.count,1)
set rng1 = rng.specialcells(xlVisible)
if not rng1 is nothing then
' there are visible rows besides the header
for each cell in rng1
msgbox cell.Address
Next
end if


As far as looping, if you working on a huge database and there would be
hundred of hidden rows between visible rows, then it probably would be an
issue. But most people are not.

--
Regards,
Tom Ogilvy



"T_o_n_y" wrote in message
...
Tom,

This response is elegant in its simplicity, but it seems to me that there
must be a better way. I have a lot that I want to do with filtered lists
in
VBA so I'm real interested in this topic.

What I mean is this: when I'm on an Excel sheet that is AutoFiltered, I
can
use the arrow keys to immediately move from one item in the filtered list
to
the next item. As far as I can tell, there's no way to do this via VBA.
The
method you mentioned in your post, for example, loops through every
invisible
cell in between just to get to the next member of the filtered list. My
question is, isn't there a way to simply move from one visible cell to
another in VBA like I can in Excel?

My understanding is the one of VBA's disadvantages is speed, and that's
why
I'm concerned with overtaxing the CPU unnecessarily.

An alternative I've come up with is to copy the filtered list to another
sheet and then step through it. Is this the best way?
"Tom Ogilvy" wrote:

do
ActiveCell.Offset(1,0).Select
Loop until activecell.EntireRow.Hidden = False


--
Regards,
Tom Ogilvy


"François" wrote in message
...
Hello,
I'm searching the correct syntax to go the the next cell(s) when
autofilter
is active.
When I do activeCell.Offset(1,0).Select, it goes to a row which is not
part
of the autofilter criterias.
Many thanks for your valuable help.

François

autofilter : how to go to next row ?
 

Tom,

I learned much from this example. Thank you. I've also learned a lot from
your other posts. I really appreciate the help.



"Tom Ogilvy" wrote:

It depends on what you want to do. I can certainly get a reference to all
the visible rows.

Dim rng as Range, rng1 as Range, cell as Range
set rng = ActiveSheet.Autofilter.Range.Columns(1)
set rng = rng.offset(1,0).Resize(rng.rows.count,1)
set rng1 = rng.specialcells(xlVisible)
if not rng1 is nothing then
' there are visible rows besides the header
for each cell in rng1
msgbox cell.Address
Next
end if


As far as looping, if you working on a huge database and there would be
hundred of hidden rows between visible rows, then it probably would be an
issue. But most people are not.

--
Regards,
Tom Ogilvy



"T_o_n_y" wrote in message
...
Tom,

This response is elegant in its simplicity, but it seems to me that there
must be a better way. I have a lot that I want to do with filtered lists
in
VBA so I'm real interested in this topic.

What I mean is this: when I'm on an Excel sheet that is AutoFiltered, I
can
use the arrow keys to immediately move from one item in the filtered list
to
the next item. As far as I can tell, there's no way to do this via VBA.
The
method you mentioned in your post, for example, loops through every
invisible
cell in between just to get to the next member of the filtered list. My
question is, isn't there a way to simply move from one visible cell to
another in VBA like I can in Excel?

My understanding is the one of VBA's disadvantages is speed, and that's
why
I'm concerned with overtaxing the CPU unnecessarily.

An alternative I've come up with is to copy the filtered list to another
sheet and then step through it. Is this the best way?
"Tom Ogilvy" wrote:

do
ActiveCell.Offset(1,0).Select
Loop until activecell.EntireRow.Hidden = False


--
Regards,
Tom Ogilvy


"François" wrote in message
...
Hello,
I'm searching the correct syntax to go the the next cell(s) when
autofilter
is active.
When I do activeCell.Offset(1,0).Select, it goes to a row which is not
part
of the autofilter criterias.
Many thanks for your valuable help.

François