Insert Sum VBA

[billandrew]

Is there a easier/shorter way to sum below each of the columns.

Right now I have -

Cells(8, 1).Value = WorksheetFunction.Sum(Range(Cells(1, 1), Cells(7, 1)))
Cells(8, 2).Value = WorksheetFunction.Sum(Range(Cells(1, 2), Cells(7, 2)))

79.2	46.2	62.7	13.4
81.3	55.5	60	3.8
103.2	69.7	79.7	3.8
93.1	50.8	87	3.1
96.3	69.3	85.8	10.7
59.4	34.4	39.6	17.8
78.6	40.1	70.4	10.2
183.5	103	164.5	6.2
130.8	74.8	107.6	20.6
132	83.4	99.6	11.6
99.3	57.7	80.5	6.9
82.9	46.1	62.8	16.4
275.6	148	198.1	35.4
285.4	163.9	181.2	24
325.4	106.3	232.4	41.5

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG11Mar38
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A:A").SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]For[/COLOR] Ac = 0 To 3
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, Ac).Areas
        Dn(Dn.Count).Offset(1).Value = Application.Sum(Dn.Value)
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[billandrew]

This works great.

Not entirely sure how this works though.

 

[billandrew]

Could you explain a bit

 

[MickG]

Sure !!
When you set the "Rng" variable as "SpecialCells(xlCellTypeConstants" each set of "Constant values" bounded by blank rows/columns is a Block of individual values.
Each block "Dn" of those value can then be looped through, as in this case.

The first "Dn" Block of cells is "A1:A7". If we find the next cell after the last cell in that range that is Dn(Dn.Count).Offset(1).Value
"Dn" being the range and Dn(dn.count) being the last cell in that range, we then want the next cell down which is ".offset(1).value, i.e "A8".

This is the cell for the sum result value and the sum value = Application.Sum(Dn.Value)
Hence:- Dn(Dn.Count).Offset(1).Value = Application.Sum(Dn.Value)

Hope that helps
Regrds Mick

 

[billandrew]

I believe I got it. Thank you very much for both.

 

[billandrew]

I see Ac is declared as long data type. Could you have started the Ac at 1 to 4? or must be 0.

 

[MickG]

It just an easy way of specifying the offset columns.
The first column that's looked at is column "A", which is the range that is specified by the variable "Rng".
So Rng.offset(,ac) , when ac is 0 is the same as "Rng" (column "A")
So Rng.offset(,ac) when ac is 1 id Column "B" etc.

 

[billandrew]

Great!!

Thanks again.