Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment cells only in filtered range?
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST? | Excel Discussion (Misc queries) | |||
Count only cells within Filtered Range | Excel Worksheet Functions | |||
sum of visable cells in filtered range | Excel Worksheet Functions | |||
average of visible cells in a filtered range | Excel Worksheet Functions | |||
Cells.Find in Auto-Filtered range | Excel Programming |