Menu
If I have an auto filter set up in Excel and I want to loop through all the visible data in one column with VBA code, what's the easiest way to do this?
All the hidden rows that have been filtered away should not be included, so a plain Range from top to bottom doesn't help.
Any good ideas?
541
You can also use the For Next loop to go through a collection of objects (such as cells or worksheets or workbooks), Here is an example that quickly enters serial numbers in all the selected cells. The above code first counts the number of selected rows and then assigns this value to the variable RowCount.
Use the following steps:First, declare a variable to refer to a worksheet for the loop. After that, start the loop with the keyword “For Each” and refer to each worksheet in the workbook. Now let's say you want to enter a value in the cell A1 of each worksheet you can use write code like following.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
