How this codes work?? Any master could explain?

Alexlin

New Member
Joined
Sep 29, 2018
Messages
15
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,736
Messages
5,597,819
Members
414,179
Latest member
anthelsene

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top