Summing Hidden Columns 1

[Mattprd]

I'm trying to enter a formula in a cell through a macro that will sum all hidden cells within a particular column, is this possible? If so, can someone supply the code?

Many thanks

Matt

 

[ide]

This line inserts a formula to the cell "C16" to sum the range above the cell C5:C15

Range("C16&quot.FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)"

ide

 

[Mattprd]

Thanks Ide but I just want to sum the cells within a range that have been hidden, any ideas?

 

[ide]

if you know the address of the range is hidden, you can insert the formula into a cell with the relative address.

f. e.: you want the formula into b1. if the hidden range is c4:e9 then

range("B1&quot.formular1c1= "SUM(R[3]C[1]:R[8]C[3])"

the example function (bellow) sums the cells in a range what has 0 height (hidden). Insert it to a module.

Function fnGetColHiddenVal(rngRef As Range)
Dim c As Range
Dim dblV As Double
On Error Resume Next
For Each c In rngRef
If c.Height = 0 Then dblV = dblV + Val(c.Value)
Next c
fnGetColHiddenVal = dblV
End Function ide

 

[SkipVought]

Hi,
This will sum all the data in all hidden columns. It can be modified as needed...

    nSum = 0
    For Each cell In ActiveSheet.UsedRange
        If cell.Columns.Hidden = True Then
            nSum = nSum + cell.Value
        End If
    Next
    MsgBox nSum

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[euskadi]

Borrowing from Skip (star for you)...

if you create a vba function like the one below, you can then insert the formula in a cell, as you requested:

Function hiddensum(ByVal target As Excel.Range)
nsum = 0
    For Each cell In target
        If cell.Columns.Hidden = True Then
            nsum = nsum + cell.Value
        End If
    Next
hiddensum = nsum
End Function

Sub insert_forumla()
    Range("J10").Select
    ActiveCell.FormulaR1C1 = "=hiddensum(RC[-5]:RC[-3])+NOW()*0"
    Range("J11").Select
End Sub

Note that this forumla recalculates any time the sheet recalculates, but it does NOT recalculate when you hide or un-hide a column. Also note that this only works if the column his HIDDEN. It does NOT work if the column has a width of 0.

 

[Mattprd]

Thanks, thats perfect