Tip Printed from ExcelTip.com
Generate total for dynamic area using VBA in Microsoft Excel


Question:
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