**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`

## Leave a Reply