Lists of data is downloaded vertically to Excel. Lists consist of different amount of numbers. At the end of each list, sum should be calculated.

B, D, F and H columns should be summed separately. Meaning, Cell B20 should sum values B2:B19, D9 sums D2:D8 and so on.

This can be done manually, of course. However, a macro can be built to make process bit faster.

The VBA is short and neat.

Sub z_dyn()

Dim zs, zl

zs = ActiveCell.Address

Selection.End(xlDown).Select

zl = ActiveCell.Address

ActiveCell.Offset(1, 0).Select

ActiveCell.Formula = “=sum(” & zs & “:” & zl & “)”

End Sub

Activate the first cell of list, like B2, and execute the macro.

Zs and zl are variables for the start cell and the last cell. The activated cell, when the macro is executed, is the first cell of list, like B2. After that the macro jumps till the last cell of list, like B19. Then macro takes one step down and populates the cell with the sum function sum(first cell:last cell).

Activate the cell B2 and execute the macro. The result is here.

In case you want to calculate the AVERAGE of each list, just change SUM to average in VBA.