Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete some column headings from an autofiltered range. | Excel Worksheet Functions | |||
Save autofiltered visible sheet to csv file? | Excel Discussion (Misc queries) | |||
How to plot only visible autofiltered rows in a data list | Charts and Charting in Excel | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) |