Easiest way to loop through a filtered list with VBA?

by

Suppose I have numbers 1 to 10 in cells A2:A11 with my autofilter in A1. I now filter to only show numbers greater than 5 (i.e. 6, 7, 8, 9, 10).

This code will only print visible cells:

Sub SpecialLoop()
    Dim cl As Range, rng As Range
    
    Set rng = Range("A2:A11")
    
    For Each cl In rng
        If cl.EntireRow.Hidden = False Then //Use Hidden property to check if filtered or not
            Debug.Print cl
        End If
    Next

End Sub

Perhaps there is a better way with SpecialCells but the above worked for me in Excel 2003.

EDIT

Just found a better way with SpecialCells:

Sub SpecialLoop()
    Dim cl As Range, rng As Range
    
    Set rng = Range("A2:A11")
    
    For Each cl In rng.SpecialCells(xlCellTypeVisible)
        Debug.Print cl
    Next cl

End Sub

More Related Contents:

  1. Format Cell Contents
  2. Excel formula checklist in employees
  3. How do I put double quotes in a string in vba?
  4. Excel VBA – How to add dynamic array formula
  5. How can I URL encode a string in Excel VBA?
  6. Excel’s fullname property with OneDrive
  7. How to insert an embedded picture?
  8. Copy Excel range as Picture to Outlook
  9. Obtain attribute names from XML using VBA
  10. Paste Excel range in Outlook
  11. Return RGB values from Range.Interior.Color (or any other Color Property)
  12. VBa conditional delete loop not working
  13. Translate text using vba
  14. Run-time error ‘1004’ : Method ‘Range’ of object’_Global’ failed
  15. “Can’t find Project or Library” for standard VBA functions
  16. How do I issue an HTTP GET from Excel VBA for Mac
  17. Copy sheet and get resulting sheet object?
  18. Sending mail using Outlook where the Send method fails
  19. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  20. Export chart as image – with click of a button
  21. Error13 in Excel VBA in data file
  22. How to get the path of current worksheet in VBA?
  23. Excel VBA to match and line up rows
  24. Change font color for a part of text in cell
  25. Create a new object using the text name of the class
  26. Excel cell from which a Function is called [duplicate]
  27. How to extract text within a string of text
  28. GET pictures from a url and then rename the picture
  29. How to highlight selected text within excel
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment