autofilter : how to go to next row ?

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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]

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.

 

[Guest]

Tom,

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