sum an array vba

Question

I have a VBA Formula to calculate the sum of visible columns.

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

This works fine for a range such as (A1:A3) but I want to hide specific columns and calculate the sum:

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

How can I loop over individual cells and add their total if they are visible??

What's wrong with SUBTOTAL or AGGREGATE to sum visible cells? — user4039065
– user4039065, Commented Jul 28, 2018 at 17:16
@jeeped they only work on rows and I need it to work on columns. — johnashu
– johnashu, Commented Jul 28, 2018 at 18:27

Magnetron · Accepted Answer · 2018-07-28 11:12:46Z

4

Use ParamArray, it allows you to pass any number of arguments:

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

answered Jul 28, 2018 at 11:12

Magnetron

8,8232 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

QHarr · Accepted Answer · 2018-07-28 12:16:45Z

2

You can use the following:

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

Aggregate: You specify first the sum argument e.g. 9 for SUM, then the additional conditions argument e.g. 7 is Ignore hidden rows and error values.

Subtotal function can also be used which automatically ignore hidden rows.

You can view the argument lists here:

edited Jul 28, 2018 at 12:16

answered Jul 28, 2018 at 11:28

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

2 Comments

johnashu Over a year ago

Sorry, I forgot to include that I wanted to hide the colums and not rows.. I have updated my question to reflect this.

QHarr Over a year ago

Did you try the above?

Collectives™ on Stack Overflow

sum an array vba

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related