Sum of row excluding hidden columns

  • Thread starter Thread starter Patrick
  • Start date Start date
P

Patrick

Hello,

I am trying to take the sum of say B5:AZ5, excluding hidden columns. The
SUBTOTAL function, using funciton #109 would allow me to do B5:B50, but not
the B5:AZ5 (it allows for taking the sum of a column, excluding hidden rows,
but not the other way around).

Is there a *decent* alternative? I have thus-far found a macro that can
handle this, but a re-calculation is needed on the user's part if a column is
hidden/unhidden. I also found a cludgey (but nonetheless interesting) way
posted by Biff on October 12, 2006, but am looking for a (a) more elegant
solution [if it exsits], and (b) a solution that does NOT require the user to
recalc (F9) the worksheet.

FWIW, I'm using Excel 2003.

Thank-you
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("K1").Calculate
End Sub

Function SumShow(R As Range) As Double
Application.Volatile
Dim cell As Range
Dim S As Double
For Each cell In R
If cell.EntireColumn.Hidden = False Then
S = S + cell
End If
Next cell
SumShow = S
End Function

This recalculates when the cursor is moved, via the mouse or keyboard.
 
Back
Top