Error stepping through Autofiltered visible range
 

I'm using a macro supplied by Tom Ogilvy which works great, except for a
small error. The code steps down through the visible cells in a single
column after using the Autofilter. It's called by another macro, which
inserts a formula.

The error comes when the ActiveCell is the last cell in the filtered visible
range. When the macro tires to increment down, it errors out at the line I
have marked below. I'm wondering what I did to Tom's code when I put in my
stuff to make it give errors.

Ed

Sub Increment1()
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 *** Debug hilites this line
rng1(1).Select
End If
End Sub

Error stepping through Autofiltered visible range
 

Sub Increment1()
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 rng1 Is Nothing Then
rng1(1).Select
End If
End Sub

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
I'm using a macro supplied by Tom Ogilvy which works great, except for a
small error. The code steps down through the visible cells in a single
column after using the Autofilter. It's called by another macro, which
inserts a formula.

The error comes when the ActiveCell is the last cell in the filtered
visible
range. When the macro tires to increment down, it errors out at the line
I
have marked below. I'm wondering what I did to Tom's code when I put in
my
stuff to make it give errors.

Ed

Sub Increment1()
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 *** Debug hilites this line
rng1(1).Select
End If
End Sub

Error stepping through Autofiltered visible range
 

Thank you, Tom. That was it.

Ed

"Tom Ogilvy" wrote in message
...
Sub Increment1()
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 rng1 Is Nothing Then
rng1(1).Select
End If
End Sub

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
I'm using a macro supplied by Tom Ogilvy which works great, except for a
small error. The code steps down through the visible cells in a single
column after using the Autofilter. It's called by another macro, which
inserts a formula.

The error comes when the ActiveCell is the last cell in the filtered
visible
range. When the macro tires to increment down, it errors out at the
line
I
have marked below. I'm wondering what I did to Tom's code when I put in
my
stuff to make it give errors.

Ed

Sub Increment1()
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 *** Debug hilites this line
rng1(1).Select
End If
End Sub