Generate total for dynamic area using VBA in Microsoft Excel

In this article, we will create a custom function to generate the total for the dynamic range.

Raw data for this article consists of random numbers.

In this example, we want to calculate the sum of values in all the cells in each column. Each column has different number of values.

We have created a custom function ‘DynaSum’ to calculate the sum of values in all the cells.

Custom function ‘DynaSum’ calculates the sum of values of all the consecutive cells. This function will sum all the values until blank cell is encountered.

If we add any additional value above defined values, then those values will get added automatically.

Code explanation

Set Rng = Application.Caller

Above code is used to assign cell containing the custom function as Range object.

Please follow below for the code

Option Explicit

Function DynaSum(Optional Rng As Range)

'Declaring variables
Dim Total As Double
Dim LngRow As Long
Dim LngCol As Integer

'Refresh function when value in worksheet is changed
Application.Volatile

'Assigning range if Range is not specified in the function
If Rng Is Nothing Then
   Set Rng = Application.Caller
End If

'Getting the column and row number
LngCol = Rng.Column
LngRow = Rng.Row - 1

'Looping through all the cells in the column until blank cell is encountered
Do Until IsEmpty(Cells(LngRow, LngCol))
   Total = Total + Cells(LngRow, LngCol)
   LngRow = LngRow - 1
   If LngRow = 1 Then Exit Do
Loop

'Getting output
DynaSum = Total

End Function

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com