Hiding/collasping a group or named range based on cell value.

Hi experts.

I'm trying to produce a report that collapses grouping or hides ranges based on a cell value. eg. I have sheet which is made up of pricing elements of a construction job. If an element is priced at £0 then then it doesn't need to appear on the report and I'd like to collapse the group automatically as opposed to clicking each one individually before printing. I have created named ranges on these grouped areas but cont find any code to hide it.

I've used the following code: (where D21 is the price total and rows 9:20 are the details of the element being priced.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("$d1:d700")) Is Nothing Then

If Range("d21").Value = 0 Then
Rows("9:20").EntireRow.Hidden = True
Else
Rows("9:20").EntireRow.Hidden = False
End If

End If


End Sub

This works until i add in an extra row within the range (as will happen when i modify in future) so i need the vba code to collapse the named range rather than the manually specified row numbers. is this possible?

Hi Ron

thanks for taking the time to respond. your solution actually answers another little issue i had elsewhere but doesn't address my original question. Its a little more complicated than that.

eg. my report shows a section for Enabling costs. within this section there are multiple elements that contribute to the total cost of the Enabling works. This is a single cell at the bottom right of the range that references another sheet and if equal to 0 then i want to collapse or hide all of the elements within the range so as to not take up space on the report. What I think i want to do is check the cell value, if its 0 then hide the named range "Range_Enabling".
see attached picture for a bit more clarity.

i could just manually click the - handle on the group to the side but there are about 30 of these sections and i'm looking to automate it as much as possible.
Screenshot (1).png

Hi Guys

I'm still stuck on this. Referring to above image I'd like to reference cell D21 and if "0" hide my named range "Range_enabling" which is currently rows 9:20. There has to be something out there that will hide the named range rather than the specific rows as by inserting an extra row within 9:20 throws the whole VBA code out and obviously moves my reference cell from D21 to D22.

I've used this code to some effect but its not really what I want to do as it feels a bit clunky.

Sub HideRows()
BeginRow = 1
EndRow = 500
ChkCol = 1

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub

I just put a 1 in the first column of all the rows I want to never hide and run the macro. Because the amounts within each pricing element reference a separate sheet they either contain an amount or have zero so all cells equal to zero hide the row. It works but I don't like it and would prefer a "cleaner" option if anyone has one.