Tom’s Tutorials For Excel: Summing Varying Ranges Along One Row or at Each Next Row

Tom’s Tutorials For Excel: Summing Varying Ranges Along One Row or at Each Next Row

Sometimes you want options in your VBA arsenal for requests to show results in ad hoc fashions.

In the picture, a table has several columns, each containing a varying count of numeric entries. You might get a request to show the sums of each column along one single row, or you might get a request to show each column’s sum immediately below the last numeric entry for that column.



Here are the macros that can accomplish both requests.

Sub SumAlongOneRow()
'Declare a Long type variable for the next available row
'where all the SUM formulas will go.
'Declare a Long type variable to identify the last column
'in the used range.
Dim NextRow As Long, LastColumn As Long
NextRow = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
LastColumn = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
'The used range starts in column A which is Column 1 in VBA.
'The sales numbers in the table start on row 5.
'Therefore, sum the numbers in a single formula that starts from
'row 5 and ends at the next available row.
Range(Cells(NextRow, 1), Cells(NextRow, LastColumn)).FormulaR1C1 _
= "=SUM(R5C:R" & NextRow - 1 & "C)"
End Sub

Sub SumEachColumnNextRow()
'Declare a Long type variable to identify the last column
'in the used range.
'Declare a Long type variable for the last row of numbers present
'in each column, below which each olumn's SUM formula will go.
'Declare a Long type variable for the column numbers that
'will be looped through.
'You will use this variable in a loop at each iteration.
Dim LastColumn As Long, lngColumn As Long, LastRow As Long
LastColumn = _
Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
'Loop through each column in the used range.
'The used range starts in column A which is Column 1 in VBA.
'Loop through each column.
For lngColumn = 1 To LastColumn
LastRow = Cells(Rows.Count, lngColumn).End(xlUp).Row
Cells(LastRow + 1, lngColumn).FormulaR1C1 = "=SUM(R5C:R" & LastRow & "C)"
Next lngColumn
End Sub