Question on Intersect and Union Method

[jamilm]

Dear Experts,

I learned how the Intersect and Union Method works in VBA. however i have never came across any project to use this referred method.

i would appreciate if someone could depict the Intersect and Union method and for what they are used for and how useful it is.


best regards,

 

[Jerry Sullivan]

Hi jamilm,

Here are a few thoughts on the topic, with links to some examples that might address your question.

Intersect
One of the most common uses of Intersect is to determine if the Target Cell or Range that
triggered a Worksheet_Change or Worksheet_Selection Event contains one or more Cells of interest.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B3")) Is Nothing Then
	'---code to take action because Cell B3 was changed
	'   ....
    End If
End Sub

Here are two examples of Intersect from recent threads:

Saving the previous value of a cell with a Data Validation dropdown list
http://www.mrexcel.com/forum/showthread.php?t=589844


Checking if a changed cell is among a non-continguous range of cells of interest
http://www.mrexcel.com/forum/showpost.php?p=2943188&postcount=15


Union
The Union Method allows you to build a range by combining two or more ranges.
Often this is used to make multi-area or non-continguous ranges, but it can also be used to make a single area range.

The Excel 2007 VBA Help provides this example:

Sub MultipleRange()
    Dim r1, r2, myMultipleRange As Range
    Set r1 = Sheets("Sheet1").Range("A1:B2")
    Set r2 = Sheets("Sheet1").Range("C3:D4")
    Set myMultipleRange = Union(r1, r2)
    myMultipleRange.Font.Bold = True
End Sub

While that is helpful to illustrate its syntax, in practice there isn't much benefit in using Union in this scenario,
because the range is constant and hard-coded into the Procedure.

If one needed to do the previous task, they could to it more simply with:

Sheets("Sheet1").Range("A1:B2,C3:D4").Font.Bold = True

Because of this, Union is more likely to be used to define a range based on variables.
For example...

Sub Highlight_Every_Nth_Row()
    Dim lCount As Long, lSpacing As Long, i As Long
    Dim myMultipleRange As Range
    
    With Application
        lCount = .InputBox("Enter number of highlighted rows", Type:=1)
        lSpacing = .InputBox("Enter number for spacing of highlighted rows", _
             Type:=1)
    End With
    
    If lCount < 2 Or lSpacing < 1 Then Exit Sub
    Set myMultipleRange = Rows(lSpacing)
    For i = 2 To lCount
        Set myMultipleRange = Union(myMultipleRange, Rows(i * lSpacing))
    Next i
    myMultipleRange.Interior.Color = vbYellow
End Sub

You can find many examples in which a Loop is used to find each cell matching a criteria,
Each time the criteria is met, something is done to that cell or its row or its column (deleted, font changed, etc),
and then the loop continues to look for the next cell.
A benefit of Union is potential efficiency in building a single range object and applying the Method or Properties once instead of repeatedly.


Here are two examples of Union from MrExcel.com threads:

Return an address of all cells within a range except those with a specified color
http://www.mrexcel.com/forum/showthread.php?t=575537

Find all cells that have formulas to then use in a find/replace operation
http://www.mrexcel.com/forum/showthread.php?t=206435


Hope this helps!