Travel to next cell immediately below, but filtered.

[StargateFanFromWork]

I've had this problem for some time and have never found a solution. When
one uses this code,

ActiveCell.Offset(1, 0).Select

one is moved to the cell immediately below a starting point cell. This
works perfectly fine when dealing with unfiltered rows. The problem arises
if one is showing only certain records. One ends up one cell below but it's
often in a hidden and not visible cell as XL2K takes this positioning
literally and does not leap over hidden ones.

What would be ideal is for the user to end up underneath the header row in
column A _but_ whose code accommocates filtered as well as unfiltered rows:
***********************************************
Sub TopOfSheetThenDownOne()
Application.Goto Reference:="R1C1"
ActiveCell.Offset(1, 0).Select
End Sub
***********************************************

So result would be that in this filtered sheet where A17 is the top visible
cell, cursor would travel up to A1 then down _one_ to A17.
If A100 were the top cell, one would go up to A1 then down _one_ to A100.
If A2 were the top cell in _UNFILTERED_ row, we'd go up to A1 then down
_one_ to A2, etc.
With the ActivelCell.Offset code, first two above would also end up in
hidden cell A2 and not where they need to be.

The idea behind this macro would be to bring the cursor out the hidden area
after filtering and to cell just below header row in column A that would
allow user to then go on to whatever s/he has to do next.

How can this be done, pls?

Thanks. D

 

[Dave Peterson]

Option Explicit
sub testme()
dim VRng as range
with activesheet.autofilter.range
if .columns(1).cells.specialcells(xlcelltypevisible).count = 1 then
msgbox "no visible cells
exit sub
end if
set Vrng = .columns(1).resize(.rows.count-1,1) _
.offset(1,0).cells.specialcells(xlcelltypevisible)
end with
vrng.cells(1).select
end sub

 

[StargateFan]

Option Explicit
sub testme()
dim VRng as range
with activesheet.autofilter.range
if .columns(1).cells.specialcells(xlcelltypevisible).count = 1 then
msgbox "no visible cells
exit sub
end if
set Vrng = .columns(1).resize(.rows.count-1,1) _
.offset(1,0).cells.specialcells(xlcelltypevisible)
end with
vrng.cells(1).select
end sub

Brilliant. That seems to work perfectly. Thank you. D