获取VBA中可见行的总和 [英] Get the Sum of visible rows in VBA

问题描述

我想要能够过滤和显示计数&全局范围和可见（过滤）范围的sumif。使用以下代码，我可以显示计数&全局范围的sumif：

I want to be able to filter and display the count & sumif of the global range and the visible (filtered) range. With the following code I am able to display the count & sumif of the global range:

AtmCount = Application.WorksheetFunction.CountIf(Range("X3:X4533"), ">0")
AtmSum = Application.WorksheetFunction.Sum(Range("X3:X4533"))

有了这个部分，我可以显示过滤范围的计数：

With this part I am able to display the count of a filtered range:

AtmCurrentCount = Range("X3:X4533").SpecialCells(xlCellTypeVisible).Count
AtmCurrentSum = ???

任何人都可以帮助我获得只有可见行的总和？

Could anyone help me to get the Sum of only the visible rows?

推荐答案

这将做你想要的。将visibleTotal设置为合适的数据类型，并更改ws和rng对象以匹配您在工作簿中的内容。

This will do what you want. Set visibleTotal to the appropriate data type for the total, and change the ws and rng objects to match what you have in your workbook.

Sub SumVisible()
    Dim ws As Worksheet
    Dim rng As Range
    Dim visibleTotal As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("B1:B7")

    ws.AutoFilterMode = False
    rng.AutoFilter field:=1, Criteria1:=5

    visibleTotal = Application.WorksheetFunction.Sum(rng.SpecialCells(xlCellTypeVisible))
    ' print to the immediate window
    Debug.Print visibleTotal
End Sub

如果您只想累加过滤范围的一部分（例如，您在A列进行过滤，但需要列B的总和），请参阅问题和答案：从筛选表的一列复制/粘贴/计算可见单元格。

In case you only want to sum part of the filtered range (e.g. you filter on column A but want the sum of column B), see this question and answer: Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table.

这篇关于获取VBA中可见行的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！