Macro to sum specific columns

[RAM72]

Hi All

For a report which has column headers in A1 to Z1.

Need to sum specific columns separately that is D2 TO F2 where data starts still to last data row , that is header D1 has its totals, E1 has its total F2 has its totals.

But however the macro should be modulable such that sometimes need to sum header C1, F1, M1 TO O1, THEN X1 , THEN Z1,all simultenous but with their respective totals

Can anyone help
Thanks

 

[Luke M]

Hi Ram,

I'm not sure how you want macro to figure out which columns get total, so it's hard-coded for now (but easy to change). Will this work?

Sub BuildTotals()
Dim myRange() As Variant
Dim c As Range
Dim lastRow As Long
Dim i As Long

'---IMPORTANT---
'Which columns get totals? List all the letters
myRange = Array("D", "E", "F")
'---------------

Application.ScreenUpdating = False

'Loop over each column in initial list
For i = LBound(myRange) To UBound(myRange)
    Set c = Cells(1, myRange(i))
   
    'Find last row in that column
    lastRow = Cells(Rows.Count, c.Column).End(xlUp).Row
   
    'Error check
    If lastRow < 2 Then lastRow = 2
   
    'Place formula
    c.FormulaR1C1 = "=SUM(R2C:R" & lastRow & "C)"
Next i

Application.ScreenUpdating = True
   
End Sub

 

[RAM72]

Hi Luke

Code works well but need to adjust the total to end of data as it overwrites total my headers

c.FormulaR1C1 = "=SUM(R2C:R" & lastRow & "C)"

Thanks if you could do needful

 

[Luke M]

Change that line to this, if you want the Total at bottom of data.

Cells(lastRow+1, c.Column).FormulaR1C1 = "=SUM(R2C:R" & lastRow & "C)"

 

[RAM72]

Change that line to this, if you want the Total at bottom of data.

Cells(lastRow+1, c.Column).FormulaR1C1 = "=SUM(R2C:R" & lastRow & "C)"

Thank you Luke