How to move down cells...

[AvWG]

Hi,

On a worksheet it is easy to move down with the cursorkeys of a keyboard,
even if the information in the worksheet is shown using filters. With the
down-arrow-key you go to the next row

With VBA moving to the next cell is easely done with
ActiveCell.OffSet(1,0).Select.

But which VBA-command do you use when your data is display with the use of a
filter? Using ActiveCell.OffSet(1,0).Select results in going to a cell/row
that isn't shown by the flter..

Who can help me?

Thanks,

André

 

[Dave Peterson]

I would just do it in a loop, checking to see if the row was visible after each
move.

do
activecell.offset(1,0).select
if activecell.entirerow.hidden = false then
exit do 'found a visible cell
end if
loop

 

[Mike H]

Hi,

It almost certainly isn't necessary to select to do what you want but this
loops through the visible rows of a filtered column (Col A in this case)
selects the row and a message box gives the address

Sub Loop_Visible()
Dim C As Range, MyRange As Range
Dim VisRange As Range
Set MyRange = ActiveSheet.AutoFilter.Range.Columns(1)
Set MyRange = MyRange.Offset(1, 0).Resize(MyRange.Rows.Count - 1, 1)
Set VisRange = MyRange.SpecialCells(xlVisible)
For Each C In VisRange
'do things
C.Select 'probably not necessary
MsgBox C.Address
Next
End Sub

Mike

 

[AvWG]

Hi Dave,

Just what I was looking for, fantastic!!!

You saved my day!

Thanks,

André from Holland

 

[Mike H]

Another way which is a bit messy is this

Range("A1").Select
Do
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.EntireRow.Hidden = True

'do things

Do
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.EntireRow.Hidden = True

'Do things etc

Mike

 

[Mike H]

Hi,

I posted a similar answer to this which I noted is a messy solution. The
problem with it is you won't know when you come out of the filtered rsngr
into an unfiltered area and could end up processing lines that you don't
intend to or want to.

Mike

 

[Dave Peterson]

You could check:

do
activecell.offset(1,0).select

if activecell.entirerow.hidden = false then
exit do 'found a visible cell
end if

loop

if intersect(activecell, activesheet.filter.range) is nothing then
msgbox "out of the filtered range"
end if