Loop through each cell in a range of cells when given a Range object

Question

Let s say I have the following code   Sub TestRangeLoop       Dim rng As Range     Set rng   Range  A1 A6            Insert code to loop through rng here End Sub   I want to be able to iterate through a collection of Range objects for each cell specified in rng   Conceptually  I d like to do it like so   For Each rngCell As Range in rng          Do something with rngCell Next   I know I could solve this by parsing rng Address and building Range objects manually  but I m hoping there is a more direct way that doesn t involve string parsing

User · Accepted Answer

Sub LoopRange        Dim rCell As Range     Dim rRng As Range      Set rRng   Sheet1 Range  A1 A6        For Each rCell In rRng Cells         Debug Print rCell Address  rCell Value     Next rCell  End Sub

User · Answer

I m resurrecting the dead here  but because a range can be defined as  A A   using a for each loop ends up with a potential infinite loop  The solution  as far as I know  is to use the Do Until loop   Do Until Selection Value        Rem Do things here    Loop

User · Answer

You could use Range Rows  Range Columns or Range Cells  Each of these collections contain Range objects   Here s how you could modify Dick s example so as to work with Rows   Sub LoopRange        Dim rCell As Range     Dim rRng As Range      Set rRng   Sheet1 Range  A1 A6        For Each rCell In rRng Rows         Debug Print rCell Address  rCell Value     Next rCell  End Sub   And Columns   Sub LoopRange        Dim rCell As Range     Dim rRng As Range      Set rRng   Sheet1 Range  A1 A6        For Each rCol In rRng Columns         For Each rCell In rCol Rows             Debug Print rCell Address  rCell Value         Next rCell     Next rCol  End Sub

User · Answer

To make a note on Dick s answer  this is correct  but I would not recommend using a For Each loop  For Each creates a temporary reference to the COM Cell behind the scenes that you do not have access to  that you would need in order to dispose of it     See the following for more discussion    How do I properly clean up Excel interop objects   To illustrate the issue  try the For Each example  close your application  and look at Task Manager  You should see that an instance of Excel is still running  because all objects were not disposed of properly    A cleaner way to handle this is to query the spreadsheet using ADO   http   technet microsoft com en-us library ee692882 aspx

[excel] Loop through each cell in a range of cells when given a Range object

Examples related to excel

Examples related to vba