Union and in Intersection in Excel VBA

The word "UNION" defines its meaning: joining one or more things together. The VBA (Virtual Basic for Application) Union means joining two or more ranges. And the Union function is much similar to the range function in Microsoft Excel.

Besides, it is considered one of the most common situations in our work when we primarily need to combine one or more ranges. The Union function comes in very handy in those situations effectively.

Moreover, as explained above, the respective VBA (Virtual Basic for Application) Union functions are purposely used to combine one or more of the efficient ranges. We can also use this function to connect the ranges that have some specific kind of standard criteria.

Let us take an example. If our data has a value less than a specific value, in that case, we can use this function to combine those particular ranges and then highlight them respectively.

Syntax of VBA Union in Excel

The syntax for the respective Union function is depicted in the below-attached screenshot,

So, for example, in case if we want to combine a range that is from A1: A5 and B1: B5, then, in that case, we will make use of the below following formula that is as follows:

Furthermore, we can do much more with this function and then see the various examples of how to use this function (Union function) in the VBA (Virtual Basic for Application).

First, let us ensure that we have a developer's tab enabled from the files account in the given options section to effectively use the VBA (Virtual Basic for the Application) in Microsoft Excel.

How can we make use of the VBA Union Function in Microsoft Excel?

Now on moving further, we will learn how to use the Virtual Basic for Application (VBA) Union functions with the help of some examples in Microsoft Excel.

#Example: 1 VBA Union in Excel

In this particular example, let us first try to select two ranges altogether, and then we will choose the A1: A5 and B1: B5 Range together in this respective example.

After that, we will follow the steps below to use the VBA ( Virtual Basic for Application) Union Function in Microsoft Excel.

Step 1: In the very first step, we need to open out the VB (Virtual Basic) editor from the visual basic that is efficiently present in the Developer's tab, as seen in the below-attached screenshot.

Step 2: After that, once we are in VB (Virtual Basic) Editor, we must go ahead and insert a new module from that particular insert section. And the module we have inserted, double-click on it so that we can primarily start writing code on that module effectively, as shown in the screenshot below.

Step 3:After that, once we get to enter into the code window, then it is necessary to name out the macro; then, as seen below,

Code:

Step 4: Now, after that, we will be working with sheet one, and then we need to activate it first to use its properties efficiently.

Code:

Step 5:Now, after that, we will make use of the union function to combine the two ranges, which we have discussed above, with the below-mentioned code.

Code:

Step 6:And once we execute the code above, we can see in sheet 1 that those two particular ranges are in our selection. Press F5 or do it manually from a run button to see the following result or the outcomes, as depicted in the below-attached figure.

An intersection in VBA (Virtual Basic for Applications)

It was known that the VBA (Virtual Basic for Application) Intersect in mathematics or geometry means when two or more of the lines or when the area crosses each other. The common point or area created after that is termed the Intersection point or place. And in Microsoft Excel, we can also highlight as well as measure out the Intersect area respectively.

Syntax of VBA Union in Excel

The syntax for the respective Intersection function is depicted in the below-attached screenshot,

From the above-attached screenshot, we can see that the respective Arg1 and Arg2 are mentioned in Range. And the rest of the arguments are enclosed in the brackets, which means the first two arguments need to be selected as the "Range", or in other words, we can say that at least a minimum 2 areas must be included to find out the Intersect, and the rest of the arguments can be selected as Range, or it can be included in some other things or parameters as well as per the requirements and the needs respectively.

And the above syntax can accommodate a maximum of 30 Arguments as well.

How to make use of the Excel VBA Intersect Function?

Now on moving further, we will learn how to effectively use the VBA Intersect functions with the help of the example.

# Example: 1 VBA Intersect in Excel

In this particular example, we will now highlight and create the Intersection area when we have some dataset. And for this, we have sample data that will have 3 columns filled with numbers, as depicted in the below-attached figure.

After that, we need to find the intersection area of the above data table with the help of the VBA Intersect. And for this, we will now follow the below steps very carefully:

Step 1: First of all, we are moving to the VBA window, and then after that, we will open a Module from the Insert menu option, as clearly depicted in the below-attached screenshot.

And then, we will get a blank window of the module.

Step 2:In this step, we will write out the Subcategory of the VBA Intersect or any other name per our needs and choice.

Code:

Step 3:After that, we will directly insert the Intersect command, as seen in the screenshot below.

Code:

In the above module, we have already explained the detailed syntax of the respective Intersect in the VBA excels, and then we will add an intersection area.

Besides these, we can also choose N ranges, but a minimum of two Ranges must be there or needed.

Now let us consider below an area of intersection: the first area is from A1 to B8, the second is from B3 to C12, and the third is from A7 to C10. And then, after that, we can consider and choose any combination of the pattern of the particular intersections.

Now, let us see at what point (/s) these particular areas should meet and intersect each other. And the common area created by all the above sites will be our intersection area.

Step 4: Soon after that, in the VBA Module, we will select the first area range, as seen in the screenshot below.

Code:

And we have added the first Range, but our syntax still needs to be completed.

Step 5: Now, on further moving, we will be inserting the rest of the two areas which we have discussed above, and commas primarily separate them.

Code:

Step 6: Now, we will give the condition "True" in this step.

Code:

This will complete our code, respectively.

Step 7: This will compile the code and then run by clicking on the Play button located just below the menu bar, as depicted in the screenshot below.

As shown above, we will get the joint or intersected area with the value TRUE. Although we got the intersect area, that TRUE has replaced the data in the intersected area.

Step 8: Now, after that, to avoid losing this, we can easily change the background of the Color, and those typical cells to any color of our choice. And for this, after the Intersect syntax, we can effectively use the Interior function along with Color, as seen in the attached screenshot below.

Code:

Step 9: Now, in the VBA, we cannot use the name of the Colour we want to use directly. And for this, we need to add "vb" that can be used to activate the colours which are available in VBA. We are selecting Green Color here as depicted in the below-attached screenshot.

Code:

Step 10: We will again compile the written code in one go, as the code is relatively small and then run it effectively.

Things to Remember

The following things need to be remembered by an individual while working with the union and intersection in the VBA excel are as follows:

1. The union is used to combine two or more of the ranges altogether.
2. And the ranges that we give to the function that must exist to avoid an error.
3. Remember to save the file in the Macro Enable Excel format so that the code will function in every use respectively.