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

 

[jpalmer_cma]

This is very close to what I want to do.

I have field names on row 4 and I filter the data many ways. Column C just has the field name "Action Required". This may be "cancel", "expedite", "reallocate", "defer receipt date", "return to vendor" depending upon a number of factors.

I created the name "ThisColumn" and entered the following formula in the "refers to" box OFFSET(Sheet1!$C$4,1,0,COUNTA(Sheet1!$A:$A)-1,1)

Then this macro worked fine.

Selection.Copy
Range("ThisColumn").Select
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste
Application.CutCopyMode = False

(I know it's not efficient and it would be better if I didn't use "select" but it works)

Tom, your approach worked great for finding the first visible row after the data had been filtered. I'd like to see how it could be modified to copy the entry from that row to all visible rows in the current column.

Thanks in advance for your assistance.