Looping through filtered range not working

[rizzo93]

As my users use a spin button to move up/down through a column of IDs, my code finds that ID which is used in many other calculations in fields and cells. BTW, this spin button is in a userform.

When that column is not filtered, everything works fine. But when the column is filtered, I want my spin button to look for the ID that's in the next visible row. I've read several threads on this subject, but I'm still not able to make it all come together.

I think at least one of my problems is not knowing how to define these visible rows as a range.

These things I know for certain:

• The first row in the range will always be >= row 11
• Even though the last row will change, it will always be <= 150.
• The column containing the IDs I'm interested in is column P

For now, I just want to figure out how to loop through the visible cells within a dynamic filtered range. Once I do, I believe I can figure out how to set the first and last row constraints.

I'd appreciate any help!

Sub testingrangeloop()
    For Each Rng In Selection.Areas <<< Normally, this will NOT be a manually selected area.
        For Each cl In Rng
        MsgBox cl.Value
       Next cl
    Next Rng
End Sub

 

[rizzo93]

Apologies, Fluff. I thought I already had answered it above.
It shows this:
$P$11:$P$150
Periscope

 

[Fluff]

Is periscope the sheet containing the filtered rows?
If so how are those rows being hidden?

 

[rizzo93]

Fluff,

Yes, the rows are on the worksheet called Periscope.

Below is the code I use to filter. Briefly, I have several hidden columns on Periscope off to the right with cell values that are either TRUE or FALSE. Each of those columns is meant for a different filter, which will filter on TRUE.

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Worksheets("Calculations").Range("P18") = ListBox1.Value

        NewFilterCol = Worksheets("Calculations").Range("P20")  'P20 CONTAINS THE COLUMN NUMBER ON PERISCOPE WHERE THE CORRESPONDING FILTER CAN BE FOUND
'        Worksheets("Calculations").Range("P16") = Worksheets("Calculations").Range("P20")
    On Error Resume Next
    ActiveSheet.Range("$B$10:$AL$194").AutoFilter     'CLEARS ANY FILTER THAT MAY ALREADY BE IN PLACE ON PERISCOPE
    ActiveSheet.Range("$B$10:$AL$194").AutoFilter Field:=NewFilterCol, Criteria1:=True 'EXECUTES THE NEWLY SELECTED FILTER
End Sub

Thanks for sticking with me.

 

[Fluff]

Apologies for the daft question, but as I cannot understand why it's reporting the entire range, I've got to ask.

Are you sure that Periscope is getting filtered & that there are hidden rows between row 11 & 150?

 

[rizzo93]

No worries, Fluff. At least I know I'm in good company.


Yes, the data is being filtered, causing a dynamically changing number of rows to be hidden.


Would it have something to do with the way they are being hidden? I presume Excel is considering anything filtered to mean rows that are hidden no matter how they are hidden, but I could be mistaken.


Presuming that you're testing out code on your end, I take it that you're not getting the same result as I am?

 

[Fluff]

The only way I can get the code to report the entire range is if the rows are set to a very small height, rather than being hidden by an autofilter.

 

[rizzo93]

Interesting. Ok, I'll continue chipping away at it.


As always, thanks so much for your help!