» Generate total for dynamic area using VBA in Microsoft Excel
CATEGORY - General Topics in VBA
VERSION - All Microsoft Excel Versions
With VBA a total for a dynamic area is to be formed. The first cell is the cell above the sum formula or with VBA - the active cell. The last cell is the cell before the empty cell
Answer:
Place the code below into the standard module.
Sub Calling()
MsgBox DynaSum(ActiveCell)
End Sub
Function DynaSum(Optional rng As Range)
Dim dblSum As Double
Dim lngRow As Long
Dim intCol As Integer
Application.Volatile
If rng Is Nothing Then
Set rng = Application.Caller
End If
intCol = rng.Column
lngRow = rng.Row
Do Until IsEmpty(Cells(lngRow - 1, intCol))
dblSum = dblSum + Cells(lngRow - 1, intCol)
lngRow = lngRow - 1
If lngRow = 1 Then Exit Do
Loop
DynaSum = dblSum
End Function
Book Store:
Recommended Books:
- Business Analysis with Microsoft Excel (2nd Edition)
- Special Edition Using Microsoft Office XP
- The Sweet Potato Queens' Big-Ass Cookbook and Financial Planner
- A Mathematician Plays the Stock Market
- The One Page Business Plan: Start With a Vision, Build a Company!
- Essentials of Accounting and Post Test Booklet 8, Eighth Edition
No comments have been submitted.

