在过滤数据a求和可见值时面临的问题

Question

Sub add_number()
Dim a As Currency
Dim i As Integer
lastrow = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row
a = 0
For i = lastrow To Range("g4").Offset(1, 0) Step by - 1
a = a + ActiveSheet.Cells(i, 7).Value
Next
ActiveSheet.Cells(h1).Value = a
End Sub

Answer 1

汇总过滤列

• 在Excel中，您可以使用SUBTOTAL function。

• 在VBA中，可以避免循环，只计算公式或将公式写入单元格，如以下代码所示。

Option Explicit
Sub SumFilteredColumn()

Const dRow As Long = 1 ' worksheet row
Const sCol As Long = 7 ' n-th column of the range (table)

Dim ws As Worksheet: Set ws = ActiveSheet ' improve!

' Restrict to continue only if the auto filter is turned on.
If Not ws.AutoFilterMode Then Exit Sub

' Reference the data column range.
Dim scrg As Range
With ws.AutoFilter.Range.Columns(sCol)
Set scrg = .Resize(.Rows.Count - 1).Offset(1)
End With

Dim dCol As Long: dCol = scrg.Column ' n-th column of the worksheet

' Write the sum (subtotal).
With ws.Cells(dRow, dCol)
' Write the value.
.Value = ws.Evaluate("SUBTOTAL(109," & scrg.Address & ")")
' Maybe writing a formula makes more sense.
'.Formula = "=SUBTOTAL(109," & scrg.Address(, 0) & ")"
End With
End Sub