How to Use the Subtotal Function in Excel and VBA

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:

is the cell in which the function’s return value should be shown.
is the number that determines which computation must be performed. The “List of function numbers” table above explains the possible values.
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:

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:

When you alter data using VBA code, there is no “undo” option.
The formula is hidden from view. As a result, the user may not understand what or why a certain number is presented.
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).