Summing columns of variable length within Macro
Posted by Stacey on June 12, 2001 6:36 AM
I am writing a macro and one of it's functions will be
to Sum a column of data. However, each month when I
run the macro the column of data will be a different
length (unknown). What is the easiest way to make
the macro place the Sum of the column NEXT(in the
column next) to the last item in the list.
Posted by JAF on June 12, 2001 6:53 AM
This should do what you need.
NB: in this macro, the VALUE of the sum of the column is inserted in the next blank row. If you need the SUM FORMULA, let me know and I'll revamp the code.
Sub Insert_SUM_VALUE()
'The following inserts the SUM value of all columns
'on the next blank line at the end of the column.
Application.ScreenUpdating = False
Range("A1").Select
Do
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Static sum_of_range
sum_of_range = Application.WorksheetFunction.Sum(Selection)
ActiveCell.End(xlDown).Offset(1, 0).Value = sum_of_range
ActiveCell.Offset(0, 1).Select
Loop Until ActiveCell.Offset(0, 0).Value = ""
'This section of the macro formats the last row of
'data as Currency and Bold.
Range("A1").Select
ActiveCell.End(xlDown).Select
Range(ActiveCell, ActiveCell.End(xlToRight)).Select
Selection.NumberFormat = "$#,##0.00"
Selection.Font.FontStyle = "Bold"
Range("A1").Select
Application.ScreenUpdating = True
End Sub
Posted by AB on June 12, 2001 6:57 AM
This is one method that will work even with multiple columns of variable length.
Sub QuickTotals()
r = ActiveCell.CurrentRegion.Rows.Count
Set SumRow = ActiveCell.CurrentRegion.Offset(r, 0).Resize(1)
SumRow.FormulaR1C1 = "=SUM(R[" & -r & "]C:R[-1]C)"
End Sub
Where "ActiveCell" can be any one cell in the sum range.
Regards,
AaronThe Excel Logic Page
