ActiveCell.Offset on filtered data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to do ActiveCell.Offset(1, 0).Range("A1:A1").Select on a column that
is filtered. My problem is that I want it to go to the next visible row.
When I do this currently if the row is not visible it will still offset to
the hidden row. Is there a way I can make it automatically offset to the
next visible row?

Thank you for your help.

Steven
 
Try this clever routine, I think provided by Tom Ogilvy in the past.

Sub AutoFilterSelectNext()
Dim rng As Range, Rng1 As Range
Dim iCol As Long

iCol = ActiveCell.Column
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(iCol))

If rng Is Nothing Then Exit Sub

Set rng = Range(ActiveCell.Offset(1, _
iCol - ActiveCell.Column), rng(rng.Count))

On Error Resume Next
Set Rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0

If Not Rng1 Is Nothing Then
Rng1(1).Select
End If
End Sub
 
Back
Top