Macro Sum each column across several columns

[zach9208]

I need help simplifying this code. I need to find the last row with data based on column G, then need to sum and drag this sum formula across columns G:M. I am essentially trying to create a totals row.

It would be nice to also add a bold border above the row. I know this code is clunky, so need help simplifying. It would be great if it autodetected how many columns with data. Thanks in advance.

Sub TotalRow()

Dim lastrow As Long
lastrow = Range("G8").End(xlDown).Row
    Cells(lastrow + 2, "G").formula = "=SUM(G8:G" & lastrow & ")"
    Cells(lastrow + 2, "H").formula = "=SUM(H8:H" & lastrow & ")"
    Cells(lastrow + 2, "I").formula = "=SUM(I8:I" & lastrow & ")"
    Cells(lastrow + 2, "J").formula = "=SUM(J8:J" & lastrow & ")"
    Cells(lastrow + 2, "K").formula = "=SUM(K8:K" & lastrow & ")"
    Cells(lastrow + 2, "L").formula = "=SUM(L8:L" & lastrow & ")"
    Cells(lastrow + 2, "M").formula = "=SUM(M8:M" & lastrow & ")"
End Sub

 

[L. Howard]

Does this do it?

Howard

Sub TotalRow()

Dim lastrow As Long
lastrow = Range("G8").End(xlDown).Row

Cells(lastrow + 2, "G").Resize(1, 7) = "=SUM(G8:G" & lastrow & ")"


'    Cells(lastrow + 2, "G").Formula = "=SUM(G8:G" & lastrow & ")"
'    Cells(lastrow + 2, "H").Formula = "=SUM(H8:H" & lastrow & ")"
'    Cells(lastrow + 2, "I").Formula = "=SUM(I8:I" & lastrow & ")"
'    Cells(lastrow + 2, "J").Formula = "=SUM(J8:J" & lastrow & ")"
'    Cells(lastrow + 2, "K").Formula = "=SUM(K8:K" & lastrow & ")"
'    Cells(lastrow + 2, "L").Formula = "=SUM(L8:L" & lastrow & ")"
'    Cells(lastrow + 2, "M").Formula = "=SUM(M8:M" & lastrow & ")"
End Sub

 

[tonyyy]

Sub TotalRow()
    Dim lastrow As Long
    [COLOR=#0000ff]lastrow = Cells(Rows.Count, "G").End(xlUp).Row[/COLOR]
    
    Cells(lastrow + 2, "G").Resize(1, 7) = "=SUM(G8:G" & lastrow & ")"
    [COLOR=#ff0000]With Range("G" & lastrow + 2, "M" & lastrow + 2).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThick
    End With[/COLOR]
End Sub

The code in red will "add a bold border above the [total] row."

The line in blue will find the last row even if there are blank cells between the data in Column G.

Cheers,

tonyyy

 

[zach9208]

This worked Perfectly. Any way to make column E say "Totals" on the same line? Thanks!!!

Sub TotalRow()
    Dim lastrow As Long
    [COLOR=#0000ff]lastrow = Cells(Rows.Count, "G").End(xlUp).Row[/COLOR]
    
    Cells(lastrow + 2, "G").Resize(1, 7) = "=SUM(G8:G" & lastrow & ")"
    [COLOR=#ff0000]With Range("G" & lastrow + 2, "M" & lastrow + 2).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThick
    End With[/COLOR]
End Sub

The code in red will "add a bold border above the [total] row."

The line in blue will find the last row even if there are blank cells between the data in Column G.

Cheers,

tonyyy

 

[L. Howard]

Try this, to do the auto adjust to more or less columns, Upper row border line and values only in the event you don't want formulas in the total row. If prefer formulas, then comment out or delete the .Value = .Value line.

Use either sheet number or sheet name in the Set sh =.

Howard

Sub TotalRow_Border_Cols()

Dim sh As Worksheet, rng As Range, lRow As Long, LCol As Long

Set sh = Sheets(3) 'Edit sheet number
'Set sh = Sheets("Summary") 'Edit sheet name

LCol = sh.Cells(8, Columns.Count).End(xlToLeft).Column
lRow = sh.Cells(Rows.Count, "G").End(xlUp).Row

'MsgBox "Last row is " & lRow & " " & "Last column is " & LCol

Set rng = sh.Range(sh.Cells(lRow + 2, "G"), sh.Cells(lRow + 2, LCol))

With rng
  .Formula = "=SUM(G8:G" & lRow & ")"
  .Value = .Value  ' Values only if you do not want the formulas in the cells.
End With

With rng.Borders(xlEdgeTop)
  .LineStyle = xlContinuous
  .Weight = xlThick
End With

End Sub