Trending December 2023 # How To Use Qlikview Aggr With Function And Types? # Suggested January 2024 # Top 13 Popular

You are reading the article How To Use Qlikview Aggr With Function And Types? updated in December 2023 on the website Cattuongwedding.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 How To Use Qlikview Aggr With Function And Types?

Introduction to QlikView Aggr

Aggr() is a very authoritative type of function in QlikView that approaches innovative aggregation, delivering unlimited flexibility to the designer and the consumer. QlikView Aggr() functions are implemented for producing an aggregate or a bundle of data records from the table rows, applying statistical or mathematical operations together on loading massive data. Based on actions performed on data by this aggr() function, some types or subcategories of this QlikView function are available. These functions can be used while loading the data files to the table fields in the QlikView script editor. The essential aggregate functions are AVG, SUM, MIN, MAX, ONLY, COUNT, CONCAT, FRACTILE, CORREL, SKEW, STDEV, MEDIAN & so on.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

How to Use QlikView Aggr?

Some aggregate functions can be applied as follows, taking the data stored in the local system named as chúng tôi table:

SUM() function provides the sum value of all the numeric data of the table column.

Code:

SUM(Price) as TotalPrice FROM […csv file path…] GROUP BY ProductCategory;

Output:

MAX() function provides the maximum value of the table column’s numeric data.

Code:

MAX(Price) as MaxPrice FROM […csv file path…] GROUP BY ProductCategory;

Output:

Code:

AVG(Price) FROM […csv file path…] GROUP BY ProductCategory;

Output:

MIN() function provides the average value of the table column’s numeric data.

Code:

MIN(Price) as MinPrice FROM […csv file path…] GROUP BY ProductCategory;

Output:

QlikView Aggr Function

In QlikView, Aggr() function arises under progressive aggregation and delivers outstanding flexibility to the clients and programmers.

This aggr() function will:

Allows executing aggregation performed over a group of scopes.

It assists in creating a type of a temp named table containing meaningful results, using which one can choose and execute further aggregations.

Permits to apply with dimensions and expressions.

The output of the Aggr() function may be implemented in a few other aggregated functions.

It is comparable to the clause GROUP BY available in SQL.

It is comparable to the chart expression centered on many chart dimensions.

If Aggr() is not present, you may be required to write complex scripts or even SQL queries to gain related results.

This aggr function helps select or control the table fields. It narrows the selection of fields.

It is a heavyweight resource user type and will slow down the application. Thus, implement only when necessary and ignore applying or targeting the effect in big applications. For slow performance, the calculated dimensions are also the source’s primary reason, so pre-calculates the table fields when possible in the script.

The QlikView aggr function results in an array consisting of values calculated over the defined dimensions for the expression. For instance, maximum values performed per customer and per location of fields of the table.

Types of QlikView Aggr

Different types are mentioned below:

1. Basic Aggregation Functions

Here, we can use a table’s data record for experimenting with the QlikView Aggr() function.

SUM([distinct] {Expr}): Being defined by the clause named GROUP BY, the sum function will return the sum of table records specified to a field provided. You can disregard the replicates just by using the term DISTINCT before the term Expression(Expr) is defined.

MIN(Expr, Rank): As defined by the clause named GROUP BY, the MIN function will return the smallest numeric value in the entire table records. If the Rank has a default value of 1, it corresponds to the lowermost value. Similarly, if the value is provided as 2, it will give the second lowermost value.

MAX(Expr, Rank): As defined by the clause named GROUP BY, the MAX function will return the highest numeric value upon the entire table records. If the Rank has a default value of 1, it corresponds to the uppermost value. Similarly, if the value is provided as 2, it will give the second uppermost value, and so on.

ONLY(Expr): When an expression includes only a single numeric value, that value is provided as output in this function. Otherwise, the NULL is returned.

MODE(Expr): It outputs the mode value over many records, which is the best frequently arising value of the expression. We will get NULL if more than one value is occurring similarly frequently. We can use this function to output either text values or numeric values.

Firstsortedvalue(Expr, sort-weight, n): When any expression is repeated over several data records, this function provides the initial value of the expression sorted by the equivalent sort-weight.

2. String Aggregate Functions

It is the second form of QlikView Aggregate functions with subcategories as:

MinString(Expr)

MaxString(Expr)

FirstValue(Expr)

LastValue(Expr)

Concat(Expr, delimiter, sort-weight)

3. Counter Aggregation Functions

NumericCount(Expr)

TextCount(Expr)

NullCount(Expr)

MissingCount(Expr)

Advanced Aggregation

4. Statistical Aggregation Functions

Fractile(Expr, Fractile)

Kurtosis(Expr)

Correl(X-Expr, Y-Expr)

AVG(Expr)

STDEV(Expr)

SKEW(Expr)

Median(Expr)

Linest_m(y-expr, x-expr, [, y0 [, x0]])

Linest_b(y-expr, x-expr, [, y0 [, x0]])

Linest_r2(y-expr, x-expr, [, y0 [, x0]])

Linest_a(y-expr, x-expr, [, y0 [, x0]])

5. Financial Aggregate Functions

irr(expr)

xirr(value expr, date expr)

npc(rate, expr)

xnpv(rate, value expr, date expr)

Conclusion

Most of the QlikView aggregate functions, which hold various fields, from elementary scientific to statistical and monetary functions, are covered here. You can apply and learn these QlikView Aggr() functions by practicing on the data records available in the database tables.

Recommended Articles

This is a guide to QlikView Aggr. Here we discuss the introduction and how to use QlikView aggr with function and types. You can also go through our other related articles to learn more –

You're reading How To Use Qlikview Aggr With Function And Types?

How To Use Excel Vba Or Function With Examples?

Excel VBA OR Function

Like a worksheet function excel VBA also has a logical function which is OR function. In any programming language OR function is defined as follows:

Watch our Demo Courses and Videos

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

Condition 1 OR Condition 2. If any of the given conditions happens to be true the value returned by the function is true while if both of the condition happens to be false the value returned by the function is false. OR Function can be termed as that it is opposite to AND function because in AND function both of the condition needs to be true in order to get a true value. Even if a single condition is termed as false then the whole value returned by the AND function is false. While in OR Function only one condition needs to be true in order to get TRUE as an output.

Syntax of OR Function in Excel VBA

VBA OR function has the following syntax:

{Condition 1} OR {Condition 2}

Let us use this function in VBA to have a clear mindset of how to use this function in general terms.

Note: In order to use VBA we need to have developer access enabled from the file tab.

How to Use Excel VBA OR Function?

We will learn how to use VBA OR Function with few examples in excel.

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

Example #1 – VBA OR

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

Step 1: Now once we are in VB Editor go ahead and insert a new module from the insert section.

Code:

Sub

Sample()

End Sub

Step 3: Define the four variables A B C and D as integers.

Code:

Sub

Sample()

Dim

Integer

Dim

As Integer

Dim

As Integer

Dim

As Integer

End Sub

Step 4: Define a variable X to store the value of OR Function, define it as a string.

Code:

Sub

Sample()

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As String

End Sub

Step 5: Assign Random Values to A B C and D.

Code:

Sub

Sample()

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As String

A = 10 B = 15 C = 20 D = 25

End Sub

Step 6: Define X’s Values as conditions for A B C and D.

Code:

Sub

Sample()

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As String

A = 10 B = 15 C = 20 D = 25

End Sub

Step 7: Now we will display the value of X stored in it.

Code:

Sub

Sample()

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As String

A = 10 B = 15 C = 20 D = 25 MsgBox X

End Sub

Step 8: Run the code from the run button provided in the screenshot below and then we see the following result when we run the above code.

Why we get the value as false because A is not greater than B and C is not greater than D. Both of the values of the condition were returned as false so our final output is also returned as false.

Example #2 – VBA OR

Step 1: Now once we are in VB Editor go ahead and insert a new module from the insert section.

Step 2: A code window will appear on the right-hand side of the screen. Define the subfunction as Sample1.

Code:

Sub

Sample1()

End Sub

Step 3: Define the four variables A B C and D as integers.

Code:

Sub

Sample1()

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As Integer

End Sub

Step 4: Define a variable X to store the value of OR Function, define it as a string.

Code:

Sub

Sample1()

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As String

End Sub

Step 5: Assign Random Values to A B C and D.

Sub

Sample1()

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As String

A = 10 B = 15 C = 20 D = 25

End Sub

Step 6: Define X’s Values as conditions for A B C and D.

Code:

Sub

Sample1()

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As String

A = 10 B = 15 C = 20 D = 25

End Sub

Step 7: Now we will display the value of X stored in it.

Code:

Sub

Sample1()

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As String

A = 10 B = 15 C = 20 D = 25 MsgBox X

End Sub

Step 8: Run the code above from the run button as shown and we will see the following result as we run the above code.

Why we get the value as True because A is less than B and C is not greater than D. One of the values of the condition were returned as true so our final output is also returned as true.

Example #3 – VBA OR

Now let us use OR Function in VBA with the IF function. Earlier we used another variable to store the Boolean value of OR function and display it. This time we will use a personalized message to display using or and if function.

Steps 1: Now once we are in VB Editor go ahead and insert a new module from the insert section.

Step 2: A code window will appear on the right-hand side of the screen. Define the subfunction as Sample2.

Code:

Sub

Sample2()

End Sub

Step 3: Define all the four variables A B C and D as integers and assign them random values.

Code:

Sub

Sample2()

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As Integer

A = 5 B = 10 C = 15 D = 20

End Sub

Step 4: Now write the if statement for the given variables, for example, like in the code given below,

Code:

Sub

Sample2()

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As Integer

A = 5 B = 10 C = 15 D = 20

End Sub

Code:

Sub

Sample2()

Dim

As Integer

Dim

As Integer

Dim

As Integer

Dim

As Integer

A = 5 B = 10 C = 15 D = 20 MsgBox "One of the conditions is true"

Else

MsgBox "None of the conditions is true"

End If

End Sub

Step 6: Run the above code from the run button and we will get the following result displayed.

As one of the conditions were true we have the above result.

Example #4 – VBA OR

Let use VBA OR function in a real scenario. We have the following data, Name of the employees and sales done by them. If their sales are equals to specific criteria or greater than that then they will receive the incentive or there will be no incentive for those employees. Have a look at the data below,

The incentive criteria are 10000 for this example. If the sales done by the employees is equals to or above 10000 they will receive the incentive.

Steps 1: Now once we are in VB Editor go ahead and insert a new module from the insert section.

Step 2: In the code window, declare the subfunction,

Code:

Sub

Employee()

End Sub

Step 3: Declare a variable X as Long and write the if statement as below,

Code:

Sub

Employee()

Dim

As Long

For

X = 2

10 Cells(X, 3).Value = "Incentive"

Else

Cells(X, 3).Value = "No Incentive"

End If

End Sub

Step 4: Start the loop for the next cell.

Code:

Sub

Employee()

Dim

As Long

For

X = 2

10 Cells(X, 3).Value = "Incentive"

Else

Cells(X, 3).Value = "No Incentive"

End If

End Sub

Step 5: Run the code to form the run button provided and once we have run the code check the result below,

In the If Statement, we used that if the sales are done is equals to 10000 or sales done greater than 10000 the employee will receive incentive.

Things to Remember

There are a few things we need to remember about VBA OR Function:

It is a logical function in excel or any other programming language.

It returns a logical output true or false.

It is the opposite of AND Function.

Recommended Articles

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

How To Create And Use Find Function In Sas?

Introduction to SAS Find

SAS Find is the function to find the input characters on the required user inputs. It will accept all the special characters, including operators and signs. Additionally, the length is used for calculating total characters among the searches finding the occurrence of the specified substring, and returning the same position of the strings. The argument will be single varchar and multi-byte characters for processing the data in SAS.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The SAS find is one of the functions that can be used to search the string characters finding the position of the data occurrence in specified substring returning the character position of the strings, substrings its not found in the first occurrence of the string and to return the integer value as 0 optional arguments are modified with the modifiers that specified to the one or more in dataset position.

Key Takeaways

It is the function and accepted for the I18N standard for designed SAS data.

The first occurrence of the string character is calculated and specified with the multi-varchar bytes.

So that it will process the find function in the multi-byte data.

Specified character, variables, constants, expressions, and other keywords.

The substring characters are calculated, and add the modifiers as optional parameters.

How to Use SAS Find?

In SAS, find function which helps to find the input strings for the first position and occurrence of the specified substring. It will return the substring position that cannot be found on the character string, substring, modifier, or start-position of the strings with optional arguments on the startpos of the Value. It helps to start the new search position of each character, and the same will be located in each direction from the right side. Suppose the start position of the string length is greater than the find position method to return the Value as an integer in 0.

But a lesser position of the strings will use to start the position in search strings and similar to directions for searching the left side of the strings, and it will compare to other position of the strings that is greater than the string length and starts the string yet to be completed of the string. It equals to return the 0 as the integer format and comparison of the Find function in every search character on each string whereas the FINDC function for searching the individual characters of every string. The find function of the string is calculated on each Index function for searching substrings of the character strings that do not use the modifier or first character string arguments.

Steps to Create SAS Find

Given below are the steps to create SAS find:

1. Navigate to below link.

3. Paste the below code for to creating the dataset.

4. data August1;

5. input a $1-20;

6. datalines;

7. Welcome To My Domain

8. Have a Nice day

9. Hello how are you

10. Thank you for your Patience

11. ;

12. run;

13. proc print data=August1;

14. Then we can use the find() method for to search the position of the strings.

15. data August2;

16. set August1;

17. res = find(a, “how”);

18. run;

19. Here we used the find() method to find the characters of the given strings.

SAS Find Function

SAS Find is the function to find the input characters on the required user inputs. It will accept all the special characters, including operators and signs. Additionally, the length is used for calculating total characters among the searches finding the occurrence of the specified substring and returning the exact position of the strings. The argument will be single varchar and multi-byte characters for processing the data in SAS.

Key Takeaways

It is the function and accepted for the I18N standard for designed SAS data.

The first occurrence of the string character is calculated and specified with the multi-varchar bytes.

So that it will process the find function in the multi-byte data.

Specified character, variables, constants, expressions, and other keywords.

The substring characters are calculated, and add the modifiers as optional parameters.

How to Use SAS Find?

find(variable,”string”,”i”)

Output:

Above code the find() method with the variable name, string and i. Here i denotes the parameter which helps to tell the SAS like ignoring the case for the specified input string which is followed by the parameter like “string” and sunstring. The index of the specified string character is printed on the output console.

SAS Find a Substring

We know that SAS find helps to find the character of the strings and their occurrence like that substring. The find function also checks and whether the given input string contains the specified characters or a combination of the input characters including the single character. It will operate the position of the input strings and also check the condition like whether the input string is substring or not. Finally, it will return the SAS input value as 0 in integer format.

The above screenshot helps to find the substring for the mentioned input strings here I used “and”.

Word for to find the position of the inputs and calculate each character and displayed the value in the variable.

Example of SAS Find

Given below is the example mentioned:

Code:

data August5; input inps $1-10; datalines; August is the current month September is the next month July is the previous month October is the next two month November and December is the next third and fourth month ; run; proc print data=August5; data August6; set August5; res1 = find(inps, "is","i"); run;

Output:

1. Above is the basic example for calculating and finding the string occurrence of the mentioned input strings.

2. Here first we declared the datasets on the first set.

3. Then after the creation of the dataset then we need to find the string characters of the input strings.

Here we mentioned the character is “is” along with “i” helps to ignore the cases.

Finally, we get the output result as below:

The is calculated for every occurrence of the inputs and the same will be printed on the variable output.

Frequently Asked Questions

Given below are the FAQ mentioned:

Q1. What is SAS find()?

Answer:

SAS find() is the function and helps to identify the string occurrence of the given inputs.

Q2. Mention the syntax of the SAS find().

Answer:

Find(variable, “string”, optional arguments or modifiers)

Here optional arguments or modifiers are used like “I, t,,”.

Q3. Define SAS substring in find().

Answer:

The Substring is like the character constant, variable, and expressions specified with the string characters.

Q4. How to find the specific string in SAS?

Answer:

Use find() function to find the position of the strings with required datasets and syntax.

Q5. How to find the character in SAS?

Answer:

With the help of the index function, the string characters are identified including special characters, letters, operators, etc.

Conclusion

The SAS find() is one of the pre-defined functions in SAS that helps to search the string characters, letters, etc. For to find the position of the string in each occurrence in various ways additionally include the index function to calculate the characters of each string in the datasets.

Recommended Articles

This is a guide to SAS Find. Here we discuss the introduction, how to use and steps to create SAS find, example, and FAQ respectively. You may also have a look at the following articles to learn more –

How To Use If Function In Excel

Last Updated on September 5, 2023

The IF function is probably one of the most well-known formulas for Excel, as it allows for data points to be compared between a value and what you are expecting.

IF statements are powerful because you can have two results. The first result is going to inform you of whether your comparison is true. The second result will tell you whether your comparison is false.

Here are the basics of IF functions and how you can use them in Excel.

Example Of IF Function

In column 1 we have a list of Musical Artists, and in column 2 we have Total Album Sales. For reference, we would consider any album sales over 100,000 good, and any album sales over 200,000 excellent.

Step

If Function

If we begin to write “=if(“ into cell C5, you’ll see that it now says logical_test which would represent the Total Album Sales in the second column.

Step

Use Comparison Symbols

What this says is is the value of cell B5 greater than 100,000?

Step

Use A Reference Point

To make this process even easier, you can have cells that already have this value, and then simply use this cell as a reference point. For example, we already have the value 100,000 in cell C2, so we select this instead of writing 100,000.

Step

Use The F4 Key

You can also use the F4 key which will fix the column and row. So the formula will look like this:

If it is greater than 100,000, which in our example it is, then we need to follow up with our formula so that the cell will complete an action.

Step

Use Quotation Marks

We put a comma after the value of 1000,000 and then write “Good”. You must use quotation marks when working out of Microsoft Excel, otherwise use a predetermined cell instead.

Our example now looks like this, with the Good selection being used via cell D2:

To add to this, if you want the cell to do nothing if our Total Album Sales value is less than 100,000 we can simply put in a double quotation mark, whilst closing the bracket at the end of the formula.

This will look like this:

What will happen is that in our third column, if the value of an album sales exceeds 100,000 you will see the word Good appear in those columns, and for any albums that didn’t surpass the 100,000 mark, the cell will remain blank.

You can then use this formula to determine whatever data point you are looking to highlight.

Step

Add Different Labels

In our example, instead of looking for album sales over 100,000, we can look for album sales under 50,000 and make the IF function list them as Bad.

Or we could add different labels such as exceptional if an album went over 200,000 total sales.

Common Issues

There are a couple of common errors that will occur using the IF function. One example is that if you have a “0” value in the cell, this means that there was no argument or selection.

Or perhaps you see “#NAME?” in the cell. This represents that a formula has been misspelled, and as such you must make sure you not only use punctuation marks but also you spell your values correctly.

Final Thoughts

There are many more ways it can be used, but these are the basics. Which should hopefully guide you in the right direction.

Types And Criteria With Example

Definition of Non-Controlling Interest

Start Your Free Investment Banking Course

Download Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others

Explanation

The portion of the interest is left out after the holding company’s claim. For example, Zee Ltd wants to acquire 60% of the equity shares of B Ltd, so in this case, out of 100% holding of B Ltd, 60% will be given to Zee. Now Zee will be the holding company, and the rest of the shares % which is 40%, will be considered as Non-controlling interest. They will not allow to manage any company affairs and not require interfering in the company’s decisions. Sometimes a situation arises when there are losses in the company, so in that case, the losses which apply to the It is combined subsidiary may exceed the non-controlling interest of the single subsidiary. The non-controlling interest holders get the share as per the confined % of the controlling interest they share in the company.

Example

Solution:

Particulars

Total

Holding Company

Controlling Interest (80%)

Non controlling interest ( 20%)

Share Capital 800,000.00 640,000.00 160,000.00

Reserve 60,000.00 48,000.00 12,000.00

860,000.00 688,000.00 172,000.00

Types

There are two types of non-controlling interest: direct non-controlling interest and another is Indirect non-controlling interest.

In direct non-controlling interest, the minority shareholders, i.e., those who are non-controlling interest bearing in the company, will get the profits to share of pre and post-acquisition. In contrast, in the case of indirect non-controlling interest, only post-acquisition profits are shared with the minority interest holders, and the pre-acquisition profits are not shared. The non-controlling Interest holders get a share of this distribution as per their controlling interest percentage in the company.

Criteria for Non-Controlling Interest Recording of Non-Controlling Interest

First of all, we must find that the acquisition is on which date.

Then find out the company which acquires and the company which is acquiring.

Calculation of Pre-acquisition and Post-acquisition profits are done.

Calculation of Pre-acquisition and Post-acquisition of reserves and surpluses are done.

In the next step, the distribution of profits takes place.

The Minority Interest record is under the head of Equity and Liability.

Minority Interest is separately recorded in the Balance sheet with its own name.

Advantages

Non-Controlling Interest holders can anyway get access to the company’s financial books.

Taking a small share as a minority interest holder in a very emerging business can help the growth of individual investors.

The Non-Controlling Interest holders can see the business developments and get an insider’s view to plan their investment in such a way.

In most cases, the minority interest holders gain a huge average return on their funds since they know the company’s norms.

The risk also subsides because a huge investment does not require from the minority interest holders, and thus they can enjoy the benefit of the low risk and more gains on returns.

In the case of a business sale, the minority interest holder can sell part of this stake without many legal complications.

Conclusion

It is a very wide term. Minority shareholders of the company are not allowed to participate in the company’s meetings, but sometimes they can make a decision for the board members. If the board’s performance is not satisfactory, then the minority shareholders can ask the board to take action against them. In the corporate, the minority interest holder meeting and voting can be very influential. Non-controlling Interest holder also makes huge profits and returns on their investment in the company. They make very little investment per the company’s emerging business but can gain huge profits. Non Controlling Interest also gets their share in case of an acquisition. It is given emphasis in the Balance Sheet and is shown as a separate item.

Recommended Articles

How To Use Vba Union Function In Excel?

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 in very handy in those situations.

Watch our Demo Courses and Videos

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

As explained above, VBA Union functions combine one or more ranges. We can use this function to combine ranges with some common criteria. For example, if our data has a 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 the Union function is as follows:

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”)

First, let us ensure we have a developer’s tab enabled from the files tab in the options section to start using VBA in Excel.

How to Use VBA Union Function in Excel?

We will learn how to use a VBA Union function with a 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 the VBA Union function in Excel:

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

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

Code:

Sub

sample()

End Sub

Step 4: Since we will work with sheet 1, we must activate it first to use its properties.

Code:

Sub

sample() Worksheets("Sheet1").Activate

End Sub

Step 5: Now, we will use the 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

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 the run button to see the result.

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 the format or change values once we combine and select the ranges together.

Code:

Sub

Sample1()

End Sub

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

Code:

Sub

Sample1() Worksheets("Sheet2").Activate

End Sub

Step 4: Combine two ranges, A1:B5 and C1:D5, with the range function and change the interior color to a 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

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

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

Example #3 – VBA Union

Now let’s use the 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 2: Name the macro name for this third example.

Sub

Sample2()

End Sub

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

Code:

Sub

Sample2()

Dim

rng1

Range

Dim

item

Range

End Sub

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

Range

Dim

item

Range

Set

rng1 = Union(Range("A1:C4"), Range("E1:F4"))

End Sub

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

Range

Dim

item

Range

Set

rng1 = Union(Range("A1:C4"), Range("E1:F4"))

For

Each

item

rng1

Debug

item.Address

item

End Sub

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

Application of VBA Union

The following syntax uses VBA union:

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

Here we can use as many ranges as we require.

Things to Remember

There are a few things that 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. The 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, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

Update the detailed information about How To Use Qlikview Aggr With Function And Types? on the Cattuongwedding.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!