如何对VBA中两个空单元格之间的列中的所有值求和

Question

我是 Excel VBA 的新手，所以我感谢任何帮助。我在Excel VBA中有以下代码，在具有特定条件的工作表数据中过滤数据后，它在工作表输出中创建两个单独的表。

 Dim i, LastRow
  LastRow = Sheets("data").Range("B" & Rows.Count).End(xlUp).Row
  Sheets("output").Range("A2:L500").ClearContents
  Sheets("output").Range("A" & Rows.Count).End(xlUp).Offset(1) = "first table"
   For i = 2 To LastRow
       If Sheets("data").Cells(i, "G").Value > 0 And Sheets("data").Cells(i, "G").Value   <= 2 Then
       Sheets("data").Cells(i, "G").EntireRow.Copy Destination:=Sheets("output").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
Sheets("output").Range("A" & Rows.Count).End(xlUp).Offset(3) = "second table"
For i = 2 To LastRow
If Sheets("data").Cells(i, "G").Value > 2 And Sheets("data").Cells(i, "G").Value <= 3 Then
    Sheets("data").Cells(i, "G").EntireRow.Copy Destination:=Sheets("output").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

我希望它做的是为每个表给出某个特定列（例如，C 列）的所有值的总和（分别用于表 1，分别用于表 2）。

Answer 1

无论您的表之间有一个空格还是五十个空格，此解决方案都应该有效：

Sub SumTablesByColumn(sCol As String)
Dim lLastRow As Long
Dim x As Long
Dim counter As Long
Dim lStartRow As Long
lStartRow = 1
counter = 1
lLastRow = ActiveSheet.Range(sCol & 500000).End(xlUp).Row
For x = 1 To lLastRow + 1
    If Range(sCol & x).Value <> "" And Range(sCol & x + 1).Value <> "" Then
        counter = counter + 1
    ElseIf Range(sCol & x).Value <> "" And Range(sCol & x + 1).Value = "" Then
        Range(sCol & counter + 1).Formula = "=SUM(" & sCol & lStartRow & ":" & sCol & counter & ")"
        x = x + 1
        If Range(sCol & x + 1).Value <> "" Then
            lStartRow = x + 1
            counter = x + 1
        End If
    ElseIf Range(sCol & x).Value = "" And Range(sCol & x + 1).Value <> "" Then
        lStartRow = x + 1
        counter = x + 1
    End If
Next
End Sub