Loop through each cell in a range of cells when given a Range object
The Range objects contains, group of Range.Rows, Range.Columns or Range.Cells.
Following code will explain 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
Following code will explain with 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
Use this solution :
For Each formulates an unstable reference to the COM Cell after the scenes that do not have access to, that would need in order to settle of it.
To represent the issue, try the For Each example, close the application, and see at Task Manager. Now can see that an instance of Excel is still running , in that all objects were settled properly).
By using ADO for cleaner way to hold this is to query the spreadsheet.