添加总计

Option Explicit

Sub AddTotals()

    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    ' Calculate the last row and column.
    Dim lr As Long
    lr = ws.Cells.Find("*", ws.Cells(1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    Dim lc As Long
    lc = ws.Cells.Find("*", ws.Cells(1), xlFormulas, xlPart, xlByColumns, xlPrevious, False).Column
    
    ' Reference the first column range.
    Dim fcrg As Range: Set fcrg = ws.Range("B2", ws.Cells(lr, "B"))
    ' Write the address of the first column range to a string variable.
    ' Lock the rows with '(, 0)' so the formula will work for all columns.
    Dim fcrgAddress As String: fcrgAddress = fcrg.Address(, 0)

    ' Reference the first sum cell.
    Dim cell As Range: Set cell = ws.Cells(lr + 2, "B")
    ' Calculate the number of columns.
    Dim cCount As Long: cCount = lc - cell.Column + 1
    ' Reference the sum row range.
    Dim srrg As Range: Set srrg = cell.Resize(, cCount)
    
    ' Write the formula to the sum row range.
    srrg.Formula = "=SUM(" & fcrgAddress & ")"

End Sub

更少的变量和Find方法参数（参数）

Sub AddTotalsShort()

    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    ' Calculate the last row and column.
    Dim lr As Long
    lr = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    Dim lc As Long
    lc = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
    
    ' Write the address of the first column range to a string variable.
    Dim fcrgAddress As String
    fcrgAddress = ws.Range("B2", ws.Cells(lr, "B")).Address(, 0)

    ' Reference the sum row range.
    Dim srrg As Range
    With ws.Cells(lr + 2, "B")
        Set srrg = .Resize(, lc - .Column + 1)
    End With
    
    ' Write the formula to the sum row range.
    srrg.Formula = "=SUM(" & fcrgAddress & ")"

End Sub

绝对不是专业人士，但这可能会解决你的问题。

Sub SumAllColumns()

  Dim a As Integer

  Range("B7").Select
  ActiveCell.FormulaR1C1 = "=+SUM(R[-5]C:R[-2]C)"
  Range("B7").Select

  a = Sheet1.UsedRange.Columns.Count

  Selection.AutoFill Destination:=Selection.Resize(1, a - 1)

End Sub

假设你有4行，第一次求和必须在单元格B7中，就像你的例子一样。如果行数也是可变的，那么让我知道，我会尝试改变它。

因此，您尝试创建的是一个自动检测表的最后一行和最后一列的值，对吗？如果是这种情况，请使用以下代码：

lr = Cells(Rows.Count, 1).End(xlUp).Row 'goes to the last row and then goes up to the last row with any value
lc = Cells(1, Columns.Count).End(xltoLeft).Column 'goes to the last column and then goes left to the last column with any value

真希望以前有人教过我。
关于求和部分，我不太明白你想做什么，我想你想做的是，把第二行的求和扩展到其他的路，对吗？，为什么不用循环呢？

for i = 2 to lr
 cells(i, lc + 2).Value = WorksheetFunction.Sum(Range(Cells(i, 1), Cells(i, lc)))
next i

希望对你有帮助!!