VBA Code for hiding Rows, issues with subsets

[RegularExcelUser]

Hi there, I've been building a spreadsheet to replicate how a piece of billing system functionality works. To that end, if you click certain buttons in the system, it will hide all subsequent options. To that end, I've a piece of VBA code running that replicates this for me e.g. if I set a named cell to a certain value, using an offset from that cell it will then hide the specified number of rows (see code here)

If Range("Contract_Value").Value = "Do Not Tick" Then
Range("Contract_Value").Offset(1).Resize(20).EntireRow.Hidden = True
Else
Range("Contract_Value").Offset(1).Resize(20).EntireRow.Hidden = False
End If

So in above example, setting the named cell "Contract_Value" to "Do Not Tick" will result in the next 20 rows below it being hidden. So far, so good.

However, within those 20 rows, I have options that also need to be hidden e.g. if I set my named cell "Contract_Value" to "Tick" instead of "Do Not Tick", it will leave those 20 rows unhidden. Then, within those 20 rows, I may have another named cell (let's say in row 7 for argument's sake) that if set "Do Not Tick" should then hide the next 2 rows (using same offset code as above). The problem is that if I write the code for that named cell, when I use the first piece of code above, then rows I have specified to hide depending on the value in the named cell on row 7 do not hide.

If Range("Contract_Value").Value = "Do Not Tick" Then
Range("Contract_Value").Offset(1).Resize(20).EntireRow.Hidden = True
Else
Range("Contract_Value").Offset(1).Resize(20).EntireRow.Hidden = False
End If

If Range("Group_Accounts_Value").Value = "Do Not Tick" Then
Range("Group_Accounts_Value").Offset(1).Resize(1).EntireRow.Hidden = True
Else
Range("Group_Accounts_Value").Offset(1).Resize(1).EntireRow.Hidden = False
End If

So, by way of example, "Group_Accounts_Value" is a named cell that is contained within the 20 row offset from the named cell "Contract_Value", row 8 to be precise. If I set "Contract_Value" to "Do Not Tick", it hides all the 20 rows underneath it with the exception of row 9, which is the row that would be hidden if I didn't set the "Contract_Value" named cell to "Do Not Tick" and just set the "Group_Accounts_Value" named cell to "Do Not Tick".

All suggestions greatly appreciated. While it's not crucial I get this working properly, it sure would be nice to!

 

[kevin9999]

Not sure that I'm following your requirement 100%, but try the following and see if it gives you what you want:

Sub test()
    With Range("Contract_Value")
        If .Value = "Do Not Tick" Then
            .Offset(1).Resize(20).EntireRow.Hidden = True
        Else
            .Offset(1).Resize(20).EntireRow.Hidden = False
        End If
    End With
    With Range("Group_Accounts_Value")
        If .Value = "Do Not Tick" Then
            .Offset(1).Resize(1).EntireRow.Hidden = True
        Else
            .Offset(1).Resize(1).EntireRow.Hidden = False
        End If
    End With
End Sub

 

[RegularExcelUser]

Not sure that I'm following your requirement 100%, but try the following and see if it gives you what you want:

Sub test()
    With Range("Contract_Value")
        If .Value = "Do Not Tick" Then
            .Offset(1).Resize(20).EntireRow.Hidden = True
        Else
            .Offset(1).Resize(20).EntireRow.Hidden = False
        End If
    End With
    With Range("Group_Accounts_Value")
        If .Value = "Do Not Tick" Then
            .Offset(1).Resize(1).EntireRow.Hidden = True
        Else
            .Offset(1).Resize(1).EntireRow.Hidden = False
        End If
    End With
End Sub

Thanks for the reply, Kevin, unfortunately, that did not do the trick. To expound on what I'm trying to do, let me give a simple example. I have 5 rows as follows:
Row No. Column A (Cell Name) Column B (Options)
1 Top_Tier List with 2 options "Do Not Tick" and "Tick")
2 Lower_Tier_1 List with 2 options "Do Not Tick" and "Tick")
3 Lower_Tier_1_data Freetext field for data entry
4 Lower_Tier_2 List with 2 options "Do Not Tick" and "Tick")
5 Lower_Tier_2_data Freetext field for data entry
What I would like to happen is as follows:
If I select "Do Not Tick" for Row 1 (named Top Tier), then I would like it to hide rows 2 to 5 (so effectively it removes the need for the user to make any further decisions for the following rows).
If I select "Tick" for Row 1, but select "Do Not Tick" for rows 2 and/or 4, I would like it to hide rows 3 and/or 5.
As code, I have written it using named cells and offset references because depending on other options chosen in the worksheet, these cells may not be in row 1-5 (they could be anywhere, but always in the order above). I would write that as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("Top_Tier").Value = "Do Not Tick" Then
Range("Top_Tier").Offset(1).Resize(4).EntireRow.Hidden = True
Else
Range("Top_Tier").Offset(1).Resize(4).EntireRow.Hidden = False
End If

If Range("Lower_Tier_1").Value = "Do Not Tick" Then
Range("Lower_Tier_1").Offset(1).Resize(1).EntireRow.Hidden = True
Else
Range("Lower_Tier_1").Offset(1).Resize(1).EntireRow.Hidden = False
End If

If Range("Lower_Tier_2").Value = "Do Not Tick" Then
Range("Lower_Tier_2").Offset(1).Resize(1).EntireRow.Hidden = True
Else
Range("Lower_Tier_2").Offset(1).Resize(1).EntireRow.Hidden = False
End If

End Sub

What then happens is if I select "Do Not Tick" as the option for Lower_Tier_1 and/or Lower_Tier_2, it will hide the rows specified in the offset as appropriate. However, if I select "Do Not Tick" as the option for Top_Tier_1, it will only hide rows 2 and 4, and leave rows 3 and 5 unhidden, whereas I want all 4 rows to be hidden in that scenario. Any further thoughts welcomed.

 

[kevin9999]

Understood. It's really about getting the flow of the logic right. Incidentally, you need a Worksheet_Change as opposed to a Worksheet_SelectionChange event to run this. Try the code below. I've also added 5 possible scenarios that I think might meet your requirement?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1:A2,A4"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Range("Top_Tier") = "Do Not Tick" Then
            Range("Top_Tier").Offset(1).Resize(4).EntireRow.Hidden = True
            Application.EnableEvents = True
            Exit Sub
        Else
            Range("Top_Tier").Offset(1).Resize(4).EntireRow.Hidden = False
        End If
        If Range("Lower_Tier_1") = "Do Not Tick" Then
            Range("Lower_Tier_1").Offset(1).EntireRow.Hidden = True
        Else
            Range("Lower_Tier_1").Offset(1).EntireRow.Hidden = False
        End If
        If Range("Lower_Tier_2") = "Do Not Tick" Then
            Range("Lower_Tier_2").Offset(1).Resize(1).EntireRow.Hidden = True
        Else
            Range("Lower_Tier_2").Offset(1).Resize(1).EntireRow.Hidden = False
        End If
        Application.EnableEvents = True
    End If
End Sub

regular.xlsm
	A	B
1	Tick	Row 1
2	Tick	Row 2
3	Free Text	Row 3
4	Tick	Row 4
5	Free Text	Row 5
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1:A2	List	Tick,Do Not Tick
A4	List	Tick,Do Not Tick

regular.xlsm
	A	B
1	Do Not Tick	Row 1
6
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1:A2	List	Tick,Do Not Tick

regular.xlsm
	A	B
1	Tick	Row 1
2	Do Not Tick	Row 2
4	Tick	Row 4
5	Free Text	Row 5
6
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1:A2	List	Tick,Do Not Tick
A4	List	Tick,Do Not Tick

regular.xlsm
	A	B
1	Tick	Row 1
2	Tick	Row 2
3	Free Text	Row 3
4	Do Not Tick	Row 4
6
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1:A2	List	Tick,Do Not Tick
A4	List	Tick,Do Not Tick

regular.xlsm
	A	B
1	Tick	Row 1
2	Do Not Tick	Row 2
4	Do Not Tick	Row 4
6
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1:A2	List	Tick,Do Not Tick
A4	List	Tick,Do Not Tick

 

[RegularExcelUser]

Thanks Kevin, I'll give this a go in a dummy file tonight and see if it works.

 

[RegularExcelUser]

Kevin, that nearly works. It works in the sense that if my named cells are in A1, A2 and A4 all the time, it will accomplish exactly what I need. However, I can't guarantee that cells A1:A5 will always be in those positions, I just know that they will always be in those relative positions to A1. The change obviously needs to be in that first line of code.

 If Not Intersect(Range("A1:A2,A4"), Target) Is Nothing Then

I'm just wondering how I could change that to make it relative as well e.g. what are in cells A1:A5 now might be in cells A6:A10 later depending on other updates the user makes to the file. In short, I can't have any fixed cell references as the file contains a lot of dynamic elements that will result in groups of cells moving down the sheet (but always being the same offset to each other).

 

[kevin9999]

how I could change that to make it relative as well

No problem with the cells containing the dropdowns (see below).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("Top_Tier,Lower_Tier_1,Lower_Tier_2"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Range("Top_Tier") = "Do Not Tick" Then
            Range("Top_Tier").Offset(1).Resize(4).EntireRow.Hidden = True
            Application.EnableEvents = True
            Exit Sub
        Else
            Range("Top_Tier").Offset(1).Resize(4).EntireRow.Hidden = False
        End If
        If Range("Lower_Tier_1") = "Do Not Tick" Then
            Range("Lower_Tier_1").Offset(1).EntireRow.Hidden = True
        Else
            Range("Lower_Tier_1").Offset(1).EntireRow.Hidden = False
        End If
        If Range("Lower_Tier_2") = "Do Not Tick" Then
            Range("Lower_Tier_2").Offset(1).Resize(1).EntireRow.Hidden = True
        Else
            Range("Lower_Tier_2").Offset(1).Resize(1).EntireRow.Hidden = False
        End If
        Application.EnableEvents = True
    End If
End Sub

 

[RegularExcelUser]

Thanks Kevin,
I'll give this a go in my big spreadsheet and see how it works out, but in theory it looks promising

 

[RegularExcelUser]

Kevin,
Thanks a million, took me a while to get it to work in my big spreadsheet, but went back and wrote everything from scratch and it worked out perfect.

 

[kevin9999]

Kevin,
Thanks a million, took me a while to get it to work in my big spreadsheet, but went back and wrote everything from scratch and it worked out perfect.

Glad it worked out, and thanks for the feedback