代码之家 › 专栏 › 技术社区 › johnashu

数组vba求和

vba excel

johnashu · 技术社区 · 6 年前

我有一个vba公式来计算可见列的总和。

Function SumVisible(WorkRng As Range) As Double

Dim rng As Range
Dim total As Double

For Each rng In WorkRng
    If rng.Rows.Hidden = False And rng.Columns.Hidden = False Then
        total = total + rng.Value
    End If
Next
SumVisible = total
End Function

在一个范围内,如 (A1:A3) 但我想隐藏特定列并计算总和:

=SumVisible(I2,K2,M2,P2,S2,U2,AB2,Y2,AE2,AI2,AL2,AQ2,AS2,AV2)

如果单个单元格可见,如何循环并添加它们的合计??

2 回复 | 直到 6 年前

Magnetron 6 年前

使用 ParamArray ,它允许您传递任意数量的参数:

Function SumVisible(ParamArray WorkRng() As Variant) As Double
    Dim i as Integer
    Dim rng As Range
    Dim c As Range
    Dim total As Double

    For i = LBound(WorkRng) To UBound(WorkRng)
        Set rng = WorkRng(i)
        For Each c In rng
            If c.Rows.Hidden = False And c.Columns.Hidden = False Then
                total = total + c.Value
            End If
        Next c
    Next i
    SumVisible = total
End Function

QHarr 6 年前

您可以使用以下内容:

Public Function SumVisible(ByVal WorkRng As Range) As Double
    Dim col As Range
    For Each col In WorkRng.Columns
        If Not col.EntireColumn.Hidden Then SumVisible = SumVisible + Application.WorksheetFunction.Aggregate(9, 7, col)
    Next col
End Function

聚合:首先为sum指定sum参数,例如9作为sum,然后其他条件参数(例如7)为ignore hidden rows和error values。

SUBTOTAL函数也可以用于自动忽略隐藏行。

您可以在此处查看参数列表: