I am trying to create a Macro that is able to recognize how many rows have values above it and only sum that range. If you look at the attached image, I need to be able to click in cells B4, B10, and B21 and execute the macro and have it only sum the values in that range.
So for cell B4, I need the macro to know that it needs to sum cells B1 to B3.
For cell B10, I need the macro to automatically know that it needs to sum the range of 4 cells (from B6 to B9).
For cell B21, I need the macro to automatically identify that the range to sum is now 9 rows (B12 to B20).
The Macros I have used will either only Sum a set range of cells (e.g. will only sum the 5 cells immediately above the cell where the macro is executed from (see code snip below):
Sub Macro22() ' ' Macro22 Macro ' With Relative References turned on. ' ' Keyboard Shortcut: Ctrl+o ' ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)" End Sub
My second attempt will cause the macro to sum all cells above it.
Sub test() ActiveCell.Value = "=SUM(" & Range(Cells(1, ActiveCell.Column), Cells(ActiveCell.Row - 1, ActiveCell.Column)).Address(False, False) & ")" End Sub
Is this even possible? From what I've seen it should be possible, but it's super tricky to get the macro to do it.