How this codes work?? Any master could explain?

[Alexlin]

Hi, thanks for visiting my post.
Basically I have the code already and they do work. But I dun understand the logic behind.
For learning purpose, anyone could teach me why the below works? Codes dun understand have been highlighted
Please assume I am a beginner.


Task: To copy some cells with interior within a specific ranges and I have the code below, which indeed works.

-----

Dim r As Range
.
.
.
For Each cell In Range(Selection, Cells(LastRow, AC))
If cell.Interior.ColorIndex = 6 Then
If r Is Nothing Then <---I do not understand this
Set r = cell <---I do not understand this
Else
Set r = Union(r, cell) <---I do not understand this
End If
End If
Next
If Not r Is Nothing Then r.Copy <---I do not understand this
End Sub

----------------

Thank you v. much

 

[Yongle]

UNION is a function to group cells
UNION must contain at least 2 ranges
- otherwise code fails
After grouping cells into range r, VBA can use a single command to do something to all cells in range r

Your code is grouping cells with interior colour index = 6 into range r and then copying those cells with r.Copy

When VBA finds the FIRST cell with colour 6, r contains no cells
IF r Is Nothing (tests to see if range r contains any cells )
WE CANNOT USE UNION
Set r = cell

ELSE ( in other words IF r Is NOT Nothing )
WE CAN USE UNION to group range r and the next found cell
Set r = UNION(r , cell )

If range r contains no cells then VBA fails when r.Copy runs
To prevent the code failing we test whether range r contains any cells
If Not r is Nothing Then r.Copy

Debug.Print
To see what is happening during the code, insert the red line
Else
Set r = Union(r, cell)
End If
Debug.Print r.Address(0,0)
End If
Next

See the results of Debug.Print in VBA Immediate Window
- Immediate window can be seen in VBA with shortcut {CTRL} G

 

[Alexlin]

Thanks Yongle! It's very clear!