Hello everyone
I have a range B7:B19 and there some blank rows say cell B13 and B19
How can I insert SUM formula so as to sum the related range
So in this example, B13 should sum range from B7:B12 .. and B19 should sum B14:B18
Insert totals in blank cells
-
- PlatinumLounger
- Posts: 4911
- Joined: 31 Aug 2016, 09:02
-
- Administrator
- Posts: 78391
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Insert totals in blank cells
Code: Select all
Sub CreateSumFormulas()
Dim rng As Range
For Each rng In Range("B7:B19").SpecialCells(xlCellTypeConstants).Areas
rng(1).Offset(rng.Count).Formula = "=SUM(" & rng.Address & ")"
Next rng
End Sub
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4911
- Joined: 31 Aug 2016, 09:02
Re: Insert totals in blank cells
Excellent approach, my tutor. Thank you very much.
-
- 2StarLounger
- Posts: 144
- Joined: 11 Jun 2012, 20:37
Re: Insert totals in blank cells
For a laugh, this still works in Office365:
Code: Select all
Range("B7:B19").SpecialCells(xlCellTypeBlanks).Select
CommandBars.ExecuteMso "AutoSum"
-
- PlatinumLounger
- Posts: 4911
- Joined: 31 Aug 2016, 09:02
Re: Insert totals in blank cells
Nice trick. Thanks a lot
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: Insert totals in blank cells
Hi,
How about changing the data to a table and using table totals? The data will then automatically expand.
Lisa
How about changing the data to a table and using table totals? The data will then automatically expand.
Lisa
-
- PlatinumLounger
- Posts: 4911
- Joined: 31 Aug 2016, 09:02
Re: Insert totals in blank cells
The totals are in different blank cells not only at the end of the table