A sneaky little cause of possible bugs is (inconsistent use of) the .Cells-property when calling Range-objects. This opening sentence should have scared of all the non-geeks :).
Let me explain why. Consider next code example:
Sub TestCells() Dim rMyRange As Range Dim rCell As Range '1. Initialize range Set rMyRange = Worksheets(1).Range("A1:B3") '2. Set range to first column only Set rMyRange = Intersect(rMyRange, rMyRange.Columns(1)) '3. Display address Debug.Print rMyRange.Address 'Iterate through the cells of the column For Each rCell In rMyRange '4. Display individual cell addresses Debug.Print rCell.Address Next End Sub
The example is simple enough: reset a range to its first column and display the adresses of the individual cells.
When you run this example this is the obvious output:
$A$1:$A$3 $A$1 $A$2 $A$3
Looks can be deceiving though. Some of you may have already noticed the cumbersome way of determining the first column part of the range.
Why not simply do it like this?:
'2. Set range to first column only Set rMyRange = rMyRange.Columns(1)
Ok smarty pants, predict the output after you changed that line of code and run the example again. Same output? Not so much:
How can this be? For reasons beyond me the default property of a returned range is sometimes different according how you went about getting it.
The function Application.Intersect returns a collection of individual cells as expected; as a default a defined ranges and its .Cells-property are identical; just comment out the line ‘Set rMyRange = rMyRange.Columns(1)’ and run the example. This is the expected output:
$A$1:$B$3 $A$1 $B$1 $A$2 $B$2 $A$3 $B$3
Apparently range-properties like Range.Columns and Range.Rows (and god knows what else) play by different rules; Range and Range.Cells no longer point at the some thing.
This is all the more confusing because the adresses of the different range-results are the same.
I ran into this issue when I was refactoring some code in Connexa and wanted to get rid of redundant Intersect-calls. The only way to tackle this inconsistency is to always call the .Cells collection of the range after you set it:
For Each rCell In rMyRange.Cells '4. Display individual cell addresses Debug.Print rCell.Address Next
Live and learn!