Sum only cells not hidden through a macro using EntireRow.Hidden command

[billfinn]

Good morning!
I am using several Select Case commands to hide different rows under differing circumstances in a macro native to a worksheet. I find myself needing to sum the visible cells which would obviously change depending on choices made in other cells. I am aware that Subtotal will work on filtered cells but can't seem to find a way to sum up rows that were not hidden. FYI B1 is a pulldown menu with choices from one to 7
I'd like to be able to just use a formula on this for simplicity. Is there a way to sum only cells that are not hidden?

This is the code I use to hide and un-hide rows

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = True
    Rows("4:17").EntireRow.Hidden = False
    Select Case Range("B1")
    Case "1"
    Rows("6:17").EntireRow.Hidden = True
    Case "2"
    Rows("8:17").EntireRow.Hidden = True
    Case "3"
    Rows("10:17").EntireRow.Hidden = True
    Case "4"
    Rows("12:17").EntireRow.Hidden = True
    Case "5"
    Rows("14:17").EntireRow.Hidden = True
    Case "6"
    Rows("16:17").EntireRow.Hidden = True
    End Select
    End Sub

Here is a screenshot of the sample file

[TABLE="width: 704"]
<tbody>[TR]
[TD]Qty of Bedrooms
[/TD]
[TD]7
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Shelving Cost
[/TD]
[TD]15.09
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]BedroomWidth
[/TD]
[TD]Bedroom Length
[/TD]
[TD] Closet Y/N
[/TD]
[TD]Closet Width
[/TD]
[TD] Type of Doors
[/TD]
[TD]Total Shelving Cost
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]14
[/TD]
[TD] YES
[/TD]
[TD]14
[/TD]
[TD] Solid Core
[/TD]
[TD] $ 211.26
[/TD]
[/TR]
[TR]
[TD]BedroomWidth
[/TD]
[TD]Bedroom Length
[/TD]
[TD] Closet Y/N
[/TD]
[TD]Closet Width
[/TD]
[TD] Type of Doors
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]12
[/TD]
[TD] YES
[/TD]
[TD]14
[/TD]
[TD] Solid Core
[/TD]
[TD] $ 211.26
[/TD]
[/TR]
[TR]
[TD]BedroomWidth
[/TD]
[TD]Bedroom Length
[/TD]
[TD] Closet Y/N
[/TD]
[TD]Closet Width
[/TD]
[TD] Type of Doors
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]12
[/TD]
[TD] YES
[/TD]
[TD]12
[/TD]
[TD] Hollow Core
[/TD]
[TD] $ 181.08
[/TD]
[/TR]
[TR]
[TD]BedroomWidth
[/TD]
[TD]Bedroom Length
[/TD]
[TD] Closet Y/N
[/TD]
[TD]Closet Width
[/TD]
[TD] Type of Doors
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]10
[/TD]
[TD] YES
[/TD]
[TD]12
[/TD]
[TD] Hollow Core
[/TD]
[TD] $ 181.08
[/TD]
[/TR]
[TR]
[TD]BedroomWidth
[/TD]
[TD]Bedroom Length
[/TD]
[TD] Closet Y/N
[/TD]
[TD]Closet Width
[/TD]
[TD] Type of Doors
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]10
[/TD]
[TD] YES
[/TD]
[TD]10
[/TD]
[TD] Bi-Fiold
[/TD]
[TD] $ 150.90
[/TD]
[/TR]
[TR]
[TD]BedroomWidth
[/TD]
[TD]Bedroom Length
[/TD]
[TD] Closet Y/N
[/TD]
[TD]Closet Width
[/TD]
[TD] Type of Doors
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]10
[/TD]
[TD] YES
[/TD]
[TD]10
[/TD]
[TD] Bi-Fiold
[/TD]
[TD] $ 150.90
[/TD]
[/TR]
[TR]
[TD]BedroomWidth
[/TD]
[TD]Bedroom Length
[/TD]
[TD] Closet Y/N
[/TD]
[TD]Closet Width
[/TD]
[TD] Type of Doors
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]10
[/TD]
[TD] YES
[/TD]
[TD]10
[/TD]
[TD] Bi-Fiold
[/TD]
[TD] $ 150.90
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks very much for any suggestions or thoughts that might help
Bill

 

[RoryA]

Use SUBTOTAL(109,your_range)
to ignore hidden as well as filtered rows.

 

[billfinn]

RoryA,
Thanks much for getting back to me.
I did try that technique and apparently in Excel 2000 109 is not a valid option. I tried using 9 in place of 109 but that only works if the rows are filtered, not if they are hidden.
thanks,
Bill

 

[RoryA]

No it wasn't available back then. (you're a long way off the current version!)

You'd need a UDF for that then, something like:

Public Function SumVisible(ByVal SumRange As Range) As Double
   Dim cell              As Range

   On Error GoTo err_handle
   Application.Volatile

   For Each cell In SumRange
      If Not cell.EntireRow.Hidden Then SumVisible = SumVisible + Application.Sum(cell)
   Next cell
   Exit Function

err_handle:
   SumVisible = CVErr(xlErrValue)

End Function

 

[billfinn]

Rory,
thanks much!
Yeah, I'm well aware lol. I spent the first two months I was here working on a project before I discovered that the PC assigned to me was the only one in the corporation with Excel 2010, all the rest have Excel 2000. Had to start almost from scratch.
Thanks for the UDF code. I tried it and it works well except for one issue. When I change the value in B1 and use Tab or Enter or any other key to move to another cell the sum doesn't update until I click again, navigate to yet another cell or press F9. I have added a line setting calculation to automatic to the function but that did not fix the issue. If you have any thoughts I would appreciate your input.

In the interim I came up with a formula that sums only the pertinent cells based on the choice selected in D34.

=IF(D34=1,H38, IF(D34=2,SUM(H38:H39), IF(D34=3, SUM(H38:H40),IF(D34=4, SUM(H38:H41), IF(D34=5, SUM(H38:H42), IF(D34=6, SUM(H38:H43),IF(D34=7,SUM(H38:H44))))))))

It's working well so I may just leave it in place for today as I have a ton of changes to finish for management review on Thursday.
Thanks much for your help!
Bill

 

[RoryA]

You could shorten that to:

=SUM(OFFSET(H38,0,0,D34,1))

assuming D34 will always have a number in it.

 

[billfinn]

Rory,
That works perfectly. No more problems.
thanks much!!
Bill