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

 

[Fluff]

How about

Sub rizzo93()
   Dim Rng As Range, Cl As Range
   
   For Each Rng In Range("P11:P150").SpecialCells(xlVisible).Areas
      For Each Cl In Rng
         MsgBox Cl.Address
      Next Cl
   Next Rng
End Sub

 

[veyselemre]

I don't know English, I wrote with google translate. I'm sorry if I got it wrong.

Sub testingrangeloop()    
    lr = Cells(Rows.Count, "P").End(3).Row
    If lr > 150 Then lr = 150
    For Each Rng In Range("P11:P" & lr).SpecialCells(xlCellTypeVisible)
        MsgBox Rng.Value
    Next Rng
End Sub

 

[rizzo93]

Hey Fluff, thanks for the assist.


Well, the code doesn't recognize which rows are visible; it goes through every single one of them.


For example, I've filtered some data to test it out. Under this circumstance, the range it should be looking through is $P$92:$P$101.

 

[Fluff]

What does the message box say with this?

Sub rizzo93()
   Dim Rng As Range, Cl As Range
   
   For Each Rng In Range("P11:P150").SpecialCells(xlVisible).Areas
         MsgBox Rng.Address & vbLf & Rng.Parent.Name
         Exit For
   Next Rng
End Sub

 

[rizzo93]

I should clarify.

While the new range is $P$92:$P$101, it's still going through the entire range: $P$11:$P$150

 

[rizzo93]

It begins with showing the address of the first cell: $P$11

I click Ok, then it shows the address for the next one: $P$12
And so on.

 

[rizzo93]

I don't know English, I wrote with google translate. I'm sorry if I got it wrong.

Sub testingrangeloop()    
    lr = Cells(Rows.Count, "P").End(3).Row
    If lr > 150 Then lr = 150
    For Each Rng In Range("P11:P" & lr).SpecialCells(xlCellTypeVisible)
        MsgBox Rng.Value
    Next Rng
End Sub

Thanks, veyselemre.

This code still goes through the hidden cells.

 

[rizzo93]

What does the message box say with this?

Sub rizzo93()
   Dim Rng As Range, Cl As Range
   
   For Each Rng In Range("P11:P150").SpecialCells(xlVisible).Areas
         MsgBox Rng.Address & vbLf & Rng.Parent.Name
         Exit For
   Next Rng
End Sub

Fluff,
Maybe this code below looks familiar to you because it's something you helped me with earlier. I added to it to achieve what I was trying to do, which was to create a string consisting of email addresses based on what cells were selected.

    For Each Rng In Selection.Areas
        For Each Cl In Rng
        projName = Cl
        distro = Application.WorksheetFunction.Index(Sheets("GhostData").Range("AV:AV"), Application.WorksheetFunction.Match(projName, Sheets("ghostdata").Range("C:C"), 0)) + "; " + distro
        projList = Cl + Chr(13) + projList
       Next Cl
    Next Rng

Instead of ctrl-clicking cells to define my range in that scenario, I now want to define it by what's left over (visible) from the filter that a user selects.

I'm not creating a string, but it's similar enough that I thought it may help.

 

[Fluff]

Care to answer the question in the post you just quoted?