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?
Bookmarks