Insert totals in blank cells

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Insert totals in blank cells

Post by YasserKhalil »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Insert totals in blank cells

Post by HansV »

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

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Insert totals in blank cells

Post by YasserKhalil »

Excellent approach, my tutor. Thank you very much.

User avatar
p45cal
2StarLounger
Posts: 142
Joined: 11 Jun 2012, 20:37

Re: Insert totals in blank cells

Post by p45cal »

For a laugh, this still works in Office365:

Code: Select all

Range("B7:B19").SpecialCells(xlCellTypeBlanks).Select
CommandBars.ExecuteMso "AutoSum"

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Insert totals in blank cells

Post by YasserKhalil »

Nice trick. Thanks a lot

LisaGreen
5StarLounger
Posts: 964
Joined: 08 Nov 2012, 17:54

Re: Insert totals in blank cells

Post by LisaGreen »

Hi,

How about changing the data to a table and using table totals? The data will then automatically expand.

Lisa

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Insert totals in blank cells

Post by YasserKhalil »

The totals are in different blank cells not only at the end of the table