Running For Each-Next on Visible Cells Only

[Dan Gesshel]

Hello.

I would like to run a For Each - Next loop on only visible cells in a range.
The range may or may not be filtered by the user. If it is filtered, I don't
want the loop to run on those cells that have been hidden.

So for example I have:

For Each Cells in MyRange
'code here
Next Cell

What do I need to put in this statement so it only looks for the visible
cells? Or is there something else I should be doing when declaring MyRange?
Anyone out there that can help me would be great.

Thanks.

Dan

 

[Chip Pearson]

Dan,

Try something like the following

Dim Rng As Range
For Each Rng In Range("MyRange").SpecialCells(xlCellTypeVisible)
Debug.Print Rng.Address
Next Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)