EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Lookup & Reference Functions VBA Union
Secondary Sidebar
VBA Lookup & Reference Functions
  • VBA Lookup & Reference
    • VBA Selecting Range
    • VBA Range
    • VBA Match
    • VBA Transpose
    • VBA Delete Row
    • VBA Range Cells
    • VBA Delete Column
    • VBA Union
    • VBA Insert Column
    • VBA Named Range
    • VBA Hyperlink
    • VBA Last Row
    • VBA Name
    • VBA OFFSET
    • VBA Hide Columns
    • VBA Selection Range
    • VBA Columns
    • VBA Insert Row
    • VBA LOOKUP
    • VBA VLOOKUP Function
    • VBA Resize
    • Compare Two Columns in Excel using VLOOKUP
    • VLOOKUP with Different Sheets

VBA Union

By Madhuri ThakurMadhuri Thakur

VBA Union

VBA Union

As the word itself suggests union means joining one or more things. In VBA Union means joining two or more ranges together. This function is similar to the range function in excel. This is the most common situation in our work when we need to combine one or more ranges with each other. Union function comes very handily in those situations.

VBA Union functions are used to combine one or more ranges as explained above. We can use this function to combine ranges that have some kind of common criteria. For example, if our data has value less than a specific value we can use this function to combine those ranges and highlight them.

Syntax of VBA Union in Excel

The syntax for Union function is as follows:

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,328 ratings)

union Syntax

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

So for example, if we want to combine a range A1: A5 and B1: B5 we will use the following formula,

Union (Range (“A1:A5”), Range (“B1:B5”)

We can do much more with this function and we will see through various examples on how to use this function in VBA.

First, let us make sure that we have a developer’s tab enabled from the files tab in the options section so that we can start using VBA in excel.

How to Use VBA Union Function in Excel?

We will learn how to use a VBA Union function with few examples in excel.

You can download this VBA Union Excel Template here – VBA Union Excel Template

Example #1 – VBA Union

In the first example let us try to select two ranges together. Let us select A1:A5 and B1:B5 range together in this example.

Follow the below steps to use VBA Union function in Excel:

Step 1: Of course we need to open VB editor from visual basic which is in the developer’s tab.

VBA Union Example 1

Step 2: Now once we are in VB Editor go ahead and insert a new module from the insert section. The module we have inserted double click on it so that we can start writing code.

Module Union Example 1.1

Step 3: Once we are in the code window, name the macro as follows,

Code:

Sub sample()

End Sub

VBA Union Example1

Step 4: Since we will be working with sheet 1 we need to activate it first in order to use its properties.

Code:

Sub sample()

Worksheets("Sheet1").Activate

End Sub

WorkSheet 1 Active

Step 5: Now we will use union function to combine the two ranges we have discussed above with the following code.

Code:

Sub sample()

Worksheets("Sheet1").Activate
Application.Union(Range("A1:A5"), Range("B1:B5")).Select

End Sub

VBA Worksheet Active 1

Step 6: Once we execute the code above we can see in sheet 1 that those two ranges are in our selection. Press F5 or do it manually from run button to see the following result.

VBA Union Example 1

In the above example, we have only selected the two ranges but we can do much more which we will learn in the next examples.

Example #2 – VBA Union

Now in this example let us select two ranges as above together and change their interior color. We can change format or change values once we combine and select the ranges together.

Step 1: Go to Insert Menu and click on the module

Module union 2

Step 2: Declare a name for the subfunction for the second example,

Code:

Sub Sample1()

End Sub

Sub Sample

Step 3: Now let us activate sheet 2 first since we are going to use the properties of sheet 2 in this example.

Code:

Sub Sample1()

Worksheets("Sheet2").Activate

End Sub

WorkSheet 2 Active

Step 4: Combine two ranges A1:B5 and C1:D5 with range function and change the interior color to dark red by the following code.

Code:

Sub Sample1()

Worksheets("Sheet2").Activate
Application.Union(Range("A1:B5"), Range("C1:D5")).Interior.Color = 255

End Sub

WorkSheet Active 2

Step 5: Execute the above and see the result in sheet 2 as follows,

VBA Union 1

We have changed the color of the ranges after combining them as we can see that they are still in selection.

Example #3 – VBA Union

Now let use union function to display the address after combining ranges. We will combine range A1:C4 and E1:F4 and display the address in the Immediate window. An immediate window is just below our code window or we can press CTRL + G to bring it up.

Step 1:  Go to Insert Menu and click on the module,

Module

Step 2: Name the macro name for this third example.

Code:

Sub Sample2()

End Sub

VBA Union Example 3.1

Step 3: Declare two variables as a range in the next step as follows.

Code:

Sub Sample2()
Dim rng1 As Range
    Dim item As Range

End Sub

VBA Union Example 3.2

Step 4: Now set an rng1 variable as the union of the range A1: C4 and E1: F4 as follows,

Code:

Sub Sample2()
Dim rng1 As Range
    Dim item As Range
    Set rng1 = Union(Range("A1:C4"), Range("E1:F4"))
End Sub

VBA Union Example 3.3

Step 5: Now use for loop to bring the address of these cells from the combined ranges by the following code,

Code:

Sub Sample2()
Dim rng1 As Range
    Dim item As Range
    Set rng1 = Union(Range("A1:C4"), Range("E1:F4"))
    For Each item In rng1
        Debug.Print item.Address
    Next item
End Sub

VBA Union Example 3.4

Step 6: Once we run the above code we can see the result in the immediate window as follows,

VBA Union 3

Application of VBA Union

VBA union is used by the following syntax:

Expression.Union(range1, range2,…..)

Here we can use as many ranges as we require.

Things to Remember

There are few things which we need to remember about the union in VBA:

  • The union is used to combine two or more ranges together.
  • The ranges we give to the function must exist to avoid an error.
  • Instead of Application. Union we can simply use the union as we are working in excel itself.

Recommended Articles

This is a guide to VBA Union. Here we discuss how to use Excel VBA Union Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Copy Paste
  2. VBA XML
  3. VBA Subscript out of Range
  4. VBA Login
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more