Repost: Dop to next visible row when filtered?
 

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

Repost: Dop to next visible row when filtered?
 

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.

--
Regards,
Tom Ogilvy




Ed wrote in message
...
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

Repost: Dop to next visible row when filtered?
 

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" wrote in message
...
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.

--
Regards,
Tom Ogilvy




Ed wrote in message
...
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

Repost: Dop to next visible row when filtered?
 

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.

--
Regards,
Tom Ogilvy

Ed wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy




Ed wrote in message
...
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

Repost: Dop to next visible row when filtered?
 

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



--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

Ed wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy




Ed wrote in message
...
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