E
Ed
This macro is supposed to increment down to the next visible cell in a
filtered range, then to the first empty cell below the filtered range. It's
based off code Tom Ogilvy gave me, but I must have changed something and I
can't find it. I call this macro within the loop of another macro. The
problem is that this doesn't stop when it hits the end of the filtered
range; instead, it loops back up to Row 1.
When stepping through, when the ActiveCell is in the last visible row of the
filtered range, on the third "Set rng =" line, "rng.count" is one more than
the row number of the ActiveCell. But when I step down to the next line
("On Error Resume Next"), I moused over rng.count and found it was 1. (I
don't understand what this "count" is counting - rows? cells? something
else?)
What I expected was the ActiveCell to become the first empty cell below the
filtered range. The calling macro then would detect the empty cell and end
its loop. What happens, though, is this macro selects back up to the top
left cell ("rng1(1).Select"), and never goes below the filtered range,
creating an endless loop.
How do I fix this to drop out of the filtered range?
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 rng1 Is Nothing Then
rng1(1).Select
End If
End Sub
filtered range, then to the first empty cell below the filtered range. It's
based off code Tom Ogilvy gave me, but I must have changed something and I
can't find it. I call this macro within the loop of another macro. The
problem is that this doesn't stop when it hits the end of the filtered
range; instead, it loops back up to Row 1.
When stepping through, when the ActiveCell is in the last visible row of the
filtered range, on the third "Set rng =" line, "rng.count" is one more than
the row number of the ActiveCell. But when I step down to the next line
("On Error Resume Next"), I moused over rng.count and found it was 1. (I
don't understand what this "count" is counting - rows? cells? something
else?)
What I expected was the ActiveCell to become the first empty cell below the
filtered range. The calling macro then would detect the empty cell and end
its loop. What happens, though, is this macro selects back up to the top
left cell ("rng1(1).Select"), and never goes below the filtered range,
creating an endless loop.
How do I fix this to drop out of the filtered range?
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 rng1 Is Nothing Then
rng1(1).Select
End If
End Sub