Repost: Dop to next visible row when filtered?

[Ed]

Originally posted 10/10/03:

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

 

[Tom Ogilvy]

Sub Tester1()
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))
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count))
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng Is Nothing Then
rng1(1).Select
End If
End Sub

You could add some error checking, but this will do what you want.

 

[Ed]

Wow! It takes all *THAT* just to increment down one visible cell?? Well,
okay - So I run my macro to insert the formula, then call this module to
increment - correct?

Thanks once again, Tom.

Ed

 

[Tom Ogilvy]

You could probably combine some stuff, but it isn't that much.

Yes, you can enter your formula and call that.

If you just want to enter a row oriented formula

set rng = Activesheet.Autofilter.Range.Columns(3).Cells
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
On Error Resume Next
set rng1 = rng.specialcells(xlVisible)
On Error goto 0
if not rng1 is nothing then
rng1.Formula = "=Sum(" & _
cells(rng1(1).row,8).Resize(1,10).Address(0,0) & ")"
End if

puts in the sum of columns H:Q in each visible row (for that row).
As an example.

 

[Tom Ogilvy]

Just for completeness, you can also loop

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

or
set rng = ActiveCell.Offset(1,0)
do While rng.EntireRow.Hidden = True
set rng = rng.offset(1,0)
Loop
rng.Select