Drop to next visible row when filtered?
 

I want my macro to enter a formula in a cell, then drop to the next visible
cell one row down when I'm in AutoFilter mode. I tried
ActiveCell.Offset(1, 0).Activate
but that drops it to the next row even if it's hidden by the filter. Can I
put SpecialCells(xlVisible) in here somehow?

Ed

Drop to next visible row when filtered?
 

Ed,

You can use code like the following:

Dim i As Long

i = 1
With ActiveCell
Do Until .Offset(i).EntireRow.Hidden = False
i = i + 1
Loop
.Offset(i).Select
End With


--

John Green - Excel MVP
Sydney
Australia


"Ed" wrote in message ...
I want my macro to enter a formula in a cell, then drop to the next visible
cell one row down when I'm in AutoFilter mode. I tried
ActiveCell.Offset(1, 0).Activate
but that drops it to the next row even if it's hidden by the filter. Can I
put SpecialCells(xlVisible) in here somehow?

Ed

Drop to next visible row when filtered?
 

Try something like the following:

Do
r = r + 1
If ActiveCell.Offset(r, 0).EntireRow.Hidden = True Then
Else
Exit Do
End If
Loop
ActiveCell.Offset(r, 0).Select

-----Original Message-----
I want my macro to enter a formula in a cell, then drop
to the next visible
cell one row down when I'm in AutoFilter mode. I tried
ActiveCell.Offset(1, 0).Activate
but that drops it to the next row even if it's hidden by
the filter. Can I
put SpecialCells(xlVisible) in here somehow?

Ed


.