Skip to content
WaveTechGlobal

WaveTechGlobal

Ahead of the tide

Primary Menu
  • Home
  • Tech Gurus
  • Mobile Gurus
  • Game Gurus
  • Pokemon
  • Smart Home Gurus
  • Home
  • Latest
  • How to Use the Subtotal Function in Excel and VBA
  • Latest

How to Use the Subtotal Function in Excel and VBA

Dorian Stewart December 2, 2021 6 min read
148

The SUBTOTAL function in Excel and VBA can be used to calculate the subtotal of one or more cells. This is a helpful tool when you are looking for how much something costs

The “how to put subtotal formula in excel” is a question that has been asked many times. This article will show you how to use the “subtotal function” in Excel and VBA.

Creating a Range

A “range” of cells in Microsoft Excel is a continuous set of cells that spans rows and columns. Here are some instances of valid ranges with cell references:

Any single cell, such as H54 or C3.

A section of a row D4:G4, H10:N10, etc.

A section of a column For example, D4:D9, H10:H20.

A table of any size with a mix of rows and columns For example, A2:D20, G5:J15.

The Function of Range

The range function in VBA may be used to refer to a range.

Syntax

Range(“”)

Where can I find the columns and rows/cells that make up a range?

Consider the following scenario:

In the figure below, Range(“D4:G4”) is highlighted in yellow.

In the figure below, Range(“A6:D20”) is highlighted in green.

In the figure below, Range(“G5:K19”) is highlighted in blue.

Simply alter the value of the first parameter, as shown in the first picture of this example, to utilize this feature for average, count, standard deviation, and so on.

Using VBA’s Range.Subtotal Function

Two techniques may be utilized in VBA code to use the subtotal formula used in cells.

Syntax 1:

Range()=Subtotal (GroupBy, Function, TotalList, [Replace], [PageBreaks], [SummaryBelowData])

The table below explains each of the parameters:

Name Whether a parameter is required or optional is determined by the kind of parameter. Parameter data type Description
GroupBy Required Long As a one-based integer offset, this is the field to group by.
Function Required XlConsolidationFunction The subtotal function is what it sounds like.
TotalList Required Variant This is an array of one-based field offsets that indicate which fields the subtotals are applied to.
Replace Optional Variant Existing subtotals are replaced with true values. It’s the default setting.
PageBreaks Optional Variant It’s true that page breaks should be added after each group. False is the default setting.
SummaryBelowData Optional XlSummaryRow The summary data is positioned in relation to the subtotal.

Worksheets(“Mydemo”). Activate the selection option. TotalList:=Array, Subtotal GroupBy:=1, Function:=xlSum (2, 3)

The application activates the given sheet and calculates the total of the filtered and viewable cells or ranges.

Don’t be concerned if this Range. The subtotal approach is perplexing or unclear. In VBA, we have a better way to utilize the same Subtotal function.

Syntax 2

WorksheetFunction.Range() = Application.WorksheetFunction.Range() = Application.WorksheetFunction Range(), Subtotal(, Subtotal(, Subtotal(, Subtotal(,

Where:

  1. is the cell in which the function’s return value should be shown.
  2. is the number that determines which computation must be performed. The “List of function numbers” table above explains the possible values.
  3. is the number of cells that must be taken into account for the computation.

Examples

In another cell, show the sum as a subtotal.

Let’s do the same computation as in the last case.

st demo()’declares required variables Subtle dim

‘ Calculate the subtotal value and save it in the variable subt = Application. WorksheetFunction. Subtotal(9, Range(“B2:B10”)) subtotal(9, Range(“B2:B10”) subtotal(9, Range(

‘display the value of the variable in the cell Range(“B13”) = subt

End Sub

In cell B13, the result is computed and presented. If necessary, it may also be shown in a message box.

Because this is done using VBA code, the formula isn’t displayed in the ‘formula bar’ as it was in the prior instances.

st demo()’declares required variables Subtle dim

‘ Find the cheapest meal item from the list. application = subt WorksheetFunction. Subtotal(5, Range(“B2:B10”)) Subtotal(5, Range(“B2:B10”) Subtotal(5, Range(“

‘display the variable value in the cell MsgBox subt & ” Rupees is the minimum amount necessary to buy anything from this shop.”

End Sub

Please notice that the program’s function number is “5”, which stands for “Min” value.

I’ll now run the same application after filtering out the single-digit values:

Conclusion

When multiple rows/columns are concealed due to filters, the subtotal formula/function might be handy.

Advantages of this strategy include:

  1. When the value changes during the runtime of long code and this value deviates the program’s flow, using a subtotal function in VBA might be advantageous.

The disadvantages of this strategy are as follows:

  1. When you alter data using VBA code, there is no “undo” option.
  2. The formula is hidden from view. As a result, the user may not understand what or why a certain number is presented.
  3. Because this isn’t done using an Excel formula that generates values for you, the value in the cell will stay the same until you repeat your code after each new filter selection.

Filter, Filters, Formulas, Functions, Subtotal, Subtotal Formula, Subtotal Function, Sum, Sum Formula, Sum Function, VBA, VBA For Excel

Watch This Video-

Using the “vba insert subtotal formula” in Excel is a way to add up all of the cells in an entire column or row. The function can be applied to any number of cells, and it will return the sum of those cells.

{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How do I add a subtotal in Excel VBA?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”A: You can use the SUBTOTAL function, which takes a range of cells as arguments.
=SUBTOTAL(B1:B5,D6)”}},{“@type”:”Question”,”name”:”How do you use the subtotal function in Excel?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”A: To use the subtotal function in Excel, you must first select a range of cells that will make up your table by selecting them with either mouse or keyboard. Then, enter =SUBTOTAL(Cell1; Cell2) into one cell at the top of your list to create an entry for each row in which you want a subtotaled value to be entered. Next, add as many additional rows as needed and then type = before each new number and column letter combination on the last line.”}},{“@type”:”Question”,”name”:”How do you use SUM function in VBA?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”A: The SUM function is used in a variety of Microsoft Excel workbooks, and it calculates the sum total number. To use this feature on your own worksheet you would write =SUM(Sheet1!B2).”}}]}

Frequently Asked Questions

How do I add a subtotal in Excel VBA?

A: You can use the SUBTOTAL function, which takes a range of cells as arguments.
=SUBTOTAL(B1:B5,D6)

How do you use the subtotal function in Excel?

A: To use the subtotal function in Excel, you must first select a range of cells that will make up your table by selecting them with either mouse or keyboard. Then, enter =SUBTOTAL(Cell1; Cell2) into one cell at the top of your list to create an entry for each row in which you want a subtotaled value to be entered. Next, add as many additional rows as needed and then type = before each new number and column letter combination on the last line.

How do you use SUM function in VBA?

A: The SUM function is used in a variety of Microsoft Excel workbooks, and it calculates the sum total number. To use this feature on your own worksheet you would write =SUM(Sheet1!B2).

Related Tags

  • excel vba subtotal dynamic range
  • excel vba subtotal multiple columns
  • vba subtotal group by
  • group by sum in excel vba
  • excel vba subtotal total list array

Continue Reading

Previous: Uses Of Artificial Intelligence In B2B Marketing
Next: Windows 10 temporary files will not delete

Trending Now

Can Other People See Your Likes On Twitter spac 4b5bstreetjournal 1

Can Other People See Your Likes On Twitter

September 29, 2022
Can You Put Music On Your Instagram Profile or Account jeff national enquirer amazonstonebloomberg 2

Can You Put Music On Your Instagram Profile or Account

September 29, 2022
Can you find the animal on the Galapagos Islands 3

Can you find the animal on the Galapagos Islands

August 21, 2022
What is the importance of facebook messenger full screen cybersecurity proofpoint facebook facebookcimpanu 4

What is the importance of facebook messenger full screen

August 20, 2022
Clan Guide Blade and Soul Revolution 5

Clan Guide Blade and Soul Revolution

August 18, 2022
Catch ’em all with quick balls 6

Catch ’em all with quick balls

August 14, 2022

Related Stories

How Can I Increase My Energy Slowly?
5 min read
  • Latest

How Can I Increase My Energy Slowly?

January 24, 2023 30
Enjoy All the Perks of Buying the CAS-004 Exam PDF and Testing Engine
4 min read
  • Latest

Enjoy All the Perks of Buying the CAS-004 Exam PDF and Testing Engine

December 23, 2022 57
Avoid The Worst Of Human Nature
3 min read
  • Latest

Avoid The Worst Of Human Nature

October 28, 2022 125
The Most In-Demand IT Services in 2022 and Beyond
3 min read
  • Latest

The Most In-Demand IT Services in 2022 and Beyond

October 24, 2022 188
9 Amazing Tips to Make Killer Viral Instagram Videos
5 min read
  • Latest

9 Amazing Tips to Make Killer Viral Instagram Videos

October 17, 2022 153
7 Ways to Make Money Fast As a Woman
4 min read
  • Latest

7 Ways to Make Money Fast As a Woman

October 17, 2022 146

Recent Posts

  • The Leading Online Casinos For Playing Slots
  • How to Maximize Your Study Sessions Using Technology
  • How Can I Increase My Energy Slowly?
  • How to Make Your Instagram Account Stylish?
  • The Future of Television Technology

Categories

  • featurepost1 (3)
  • featurepost2 (4)
  • featurepost3 (4)
  • featurepost4 (5)
  • Game Gurus (23)
  • Latest (191)
  • Mobile Gurus (25)
  • Pokemon (66)
  • Smart Home Gurus (76)
  • Tech Gurus (26)

Tech Articles

Where is fullscreen on youtube 1

Where is fullscreen on youtube

August 18, 2022
What’s the big deal with bright covers 2

What’s the big deal with bright covers

August 14, 2022
What is Your Danganronpa 2 3

What is Your Danganronpa 2

August 11, 2022
What you need to know about GarageBand 4

What you need to know about GarageBand

August 8, 2022
What is the use of tab on ipad 5

What is the use of tab on ipad

August 6, 2022

Thanks to our partners!

  • About Us
  • Contact the Crew
  • Privacy Policy
  • T&C
Wave Tech Global © All rights reserved.
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
Do not sell my personal information.
Cookie SettingsAccept
Manage consent

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Others
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.
SAVE & ACCEPT