Generate total for dynamic area using VBA in Microsoft Excel

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/general-topics-in-vba/generate-total-for-dynamic-area-using-vba-in-microsoft-excel.html
SHARE




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
Please follow and like us:
0


Leave a Reply

Your email address will not be published. Required fields are marked *

To avoid automated spam,Please enter the value *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>