VBA - Hiding and Unhiding numerous rows based on numerous cells

LukusCarnilious

New Member
Joined
Jul 22, 2015
Messages
7
Hi,

I'm new to VBA and Macros so apologies for that!

What I have managed to achieve so far based on online forums and guides, is to hide or unhide a column and 'rows in a different sheet' based on a data validation selection in a cell.

Example:
StagesYes or No
Stage 1Yes
Stage 2No
Stage 3No
Stage 4Yes

<tbody>
</tbody>


In the Yes or no boxes you select from a drop down menu






This will then hide or unhide the stage column in the following table on the same tab. It also hides or unhides all the Activities for the stage in another table on another Tab


Stage 1Stage 2Stage 3Stage 4
Activity 1YesNoNoYes
Activity 2
Activity 3
Activity 4
Activity 5
Activity 6
Activity 7
Activity 8
Activity 9

<tbody>
</tbody>


What I would like to happen when yes or no are selected in this table is to hide or unhide the activity row in the other tab which is like the table below.














Activity and stagedata
Stage 1
Activity 2
Activity 3
Activity 4
Activity 5
Stage 2
Activity 1
Activity 2
Activity 3

<tbody>
</tbody>


In this table if you have chosen a stage to be shown then you might not need every single activity as there are a lot, so you will need to hide some using the previous table.














So that probably doesn't help much but I've pasted my code below which might make some sense as to what I'm doing.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Address(True, True) = "$D$3" Then
        Select Case Target
            Case "Yes"
                Call UnhideStage1
            Case "No"
                Call HIDEStage1
            Case Else
                Call UnhideStage1
        End Select
    End If
    If Target.Address(True, True) = "$D$4" Then
        Select Case Target
            Case "Yes"
                Call UnhideStage2
            Case "No"
                Call HIDEStage2
            Case Else
                Call UnhideStage2
        End Select
    End If
    If Target.Address(True, True) = "$D$5" Then
        Select Case Target
            Case "Yes"
                Call UnhideStage3a
            Case "No"
                Call HIDEStage3a
            Case Else
                Call UnhideStage3a
        End Select
    End If
    If Target.Address(True, True) = "$D$6" Then
        Select Case Target
            Case "Yes"
                Call UnhideStage3b
            Case "No"
                Call HIDEStage3b
            Case Else
                Call UnhideStage3b
        End Select
    End If
    If Target.Address(True, True) = "$D$7" Then
        Select Case Target
            Case "Yes"
                Call UnhideStage4a
            Case "No"
                Call HIDEStage4a
            Case Else
                Call UnhideStage4a
        End Select
    End If
    If Target.Address(True, True) = "$D$8" Then
        Select Case Target
            Case "Yes"
                Call UnhideStage4b
            Case "No"
                Call HIDEStage4b
            Case Else
                Call UnhideStage4b
        End Select
    End If
    If Target.Address(True, True) = "$D$9" Then
        Select Case Target
            Case "Yes"
                Call UnhideStage4c
            Case "No"
                Call HIDEStage4c
            Case Else
                Call UnhideStage4c
        End Select
    End If
    If Target.Address(True, True) = "$D$10" Then
        Select Case Target
            Case "Yes"
                Call UnhideStage5
            Case "No"
                Call HIDEStage5
            Case Else
                Call UnhideStage5
        End Select
    End If
    If Target.Address(True, True) = "$D$11" Then
        Select Case Target
            Case "Yes"
                Call UnhideStage6
            Case "No"
                Call HIDEStage6
            Case Else
                Call UnhideStage6
        End Select
    End If
    If Target.Address(True, True) = "$D$12" Then
        Select Case Target
            Case "Yes"
                Call UnhideStage7
            Case "No"
                Call HIDEStage7
            Case Else
                Call UnhideStage7
        End Select
    End If
End Sub
Then the list of macros are like so:

Code:
Sub HIDEStage1()
'
' HIDEStage1 Macro
'


'
    Application.ScreenUpdating = False
    Columns("F:F").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Fee estimate").Select
    Rows("19:115").Select
    Selection.EntireRow.Hidden = True
    Sheets("CAWS codes").Select
    Range("D3").Select
    Application.ScreenUpdating = True
End Sub
Sub UnhideStage1()
'
' UnhideStage1 Macro
'


'
    Application.ScreenUpdating = False
    Columns("F:F").Select
    Selection.EntireColumn.Hidden = False
    Sheets("Fee estimate").Select
    Rows("19:115").Select
    Selection.EntireRow.Hidden = False
    Sheets("CAWS codes").Select
    Range("D3").Select
    Application.ScreenUpdating = True
End Sub
Sub HIDEStage2()
'
' HIDEStage2 Macro
'


'
    Application.ScreenUpdating = False
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Fee estimate").Select
    Rows("116:212").Select
    Selection.EntireRow.Hidden = True
    Sheets("CAWS codes").Select
    Range("D4").Select
    Application.ScreenUpdating = True
End Sub
Sub UnhideStage2()
'
' UnhideStage2 Macro
'


'
    Application.ScreenUpdating = False
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = False
    Sheets("Fee estimate").Select
    Rows("116:212").Select
    Selection.EntireRow.Hidden = False
    Sheets("CAWS codes").Select
    Range("D4").Select
    Application.ScreenUpdating = True
End Sub
Sub HIDEStage3a()
'
' HIDEStage3a Macro
'


'
    Application.ScreenUpdating = False
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Fee estimate").Select
    Rows("213:309").Select
    Selection.EntireRow.Hidden = True
    Sheets("CAWS codes").Select
    Range("D5").Select
    Application.ScreenUpdating = True
End Sub
Sub UnhideStage3a()
'
' UnhideStage3a Macro
'


'
    Application.ScreenUpdating = False
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = False
    Sheets("Fee estimate").Select
    Rows("213:309").Select
    Selection.EntireRow.Hidden = False
    Sheets("CAWS codes").Select
    Range("D5").Select
    Application.ScreenUpdating = True
End Sub
Sub HIDEStage3b()
'
' HIDEStage3b Macro
'


'
    Application.ScreenUpdating = False
    Columns("I:I").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Fee estimate").Select
    Rows("310:406").Select
    Selection.EntireRow.Hidden = True
    Sheets("CAWS codes").Select
    Range("D6").Select
    Application.ScreenUpdating = True
End Sub
Sub UnhideStage3b()
'
' UnhideStage3b Macro
'


'
    Application.ScreenUpdating = False
    Columns("I:I").Select
    Selection.EntireColumn.Hidden = False
    Sheets("Fee estimate").Select
    Rows("310:406").Select
    Selection.EntireRow.Hidden = False
    Sheets("CAWS codes").Select
    Range("D6").Select
    Application.ScreenUpdating = True
End Sub
Sub HIDEStage4a()
'
' HIDEStage4a Macro
'


'
    Application.ScreenUpdating = False
    Columns("J:J").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Fee estimate").Select
    Rows("407:503").Select
    Selection.EntireRow.Hidden = True
    Sheets("CAWS codes").Select
    Range("D7").Select
    Application.ScreenUpdating = True
End Sub
Sub UnhideStage4a()
'
' UnhideStage4a Macro
'


'
    Application.ScreenUpdating = False
    Columns("J:J").Select
    Selection.EntireColumn.Hidden = False
    Sheets("Fee estimate").Select
    Rows("407:503").Select
    Selection.EntireRow.Hidden = False
    Sheets("CAWS codes").Select
    Range("D7").Select
    Application.ScreenUpdating = True
End Sub
Sub HIDEStage4b()
'
' HIDEStage4b Macro
'


'
    Application.ScreenUpdating = False
    Columns("K:K").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Fee estimate").Select
    Rows("504:600").Select
    Selection.EntireRow.Hidden = True
    Sheets("CAWS codes").Select
    Range("D8").Select
    Application.ScreenUpdating = True
End Sub
Sub UnhideStage4b()
'
' UnhideStage4b Macro
'


'
    Application.ScreenUpdating = False
    Columns("K:K").Select
    Selection.EntireColumn.Hidden = False
    Sheets("Fee estimate").Select
    Rows("504:600").Select
    Selection.EntireRow.Hidden = False
    Sheets("CAWS codes").Select
    Range("D8").Select
    Application.ScreenUpdating = True
End Sub
Sub HIDEStage4c()
'
' HIDEStage4c Macro
'


'
    Application.ScreenUpdating = False
    Columns("L:L").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Fee estimate").Select
    Rows("601:697").Select
    Selection.EntireRow.Hidden = True
    Sheets("CAWS codes").Select
    Range("D9").Select
    Application.ScreenUpdating = True
End Sub
Sub UnhideStage4c()
'
' UnhideStage4c Macro
'


'
    Application.ScreenUpdating = False
    Columns("L:L").Select
    Selection.EntireColumn.Hidden = False
    Sheets("Fee estimate").Select
    Rows("601:697").Select
    Selection.EntireRow.Hidden = False
    Sheets("CAWS codes").Select
    Range("D9").Select
    Application.ScreenUpdating = True
End Sub
Sub HIDEStage5()
'
' HIDEStage5 Macro
'


'
    Application.ScreenUpdating = False
    Columns("M:M").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Fee estimate").Select
    Rows("698:794").Select
    Selection.EntireRow.Hidden = True
    Sheets("CAWS codes").Select
    Range("D10").Select
    Application.ScreenUpdating = True
End Sub
Sub UnhideStage5()
'
' UnhideStage5 Macro
'


'
    Application.ScreenUpdating = False
    Columns("M:M").Select
    Selection.EntireColumn.Hidden = False
    Sheets("Fee estimate").Select
    Rows("698:794").Select
    Selection.EntireRow.Hidden = False
    Sheets("CAWS codes").Select
    Range("D10").Select
    Application.ScreenUpdating = True
End Sub
Sub HIDEStage6()
'
' HIDEStage6 Macro
'


'
    Application.ScreenUpdating = False
    Columns("N:N").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Fee estimate").Select
    Rows("795:891").Select
    Selection.EntireRow.Hidden = True
    Sheets("CAWS codes").Select
    Range("D11").Select
    Application.ScreenUpdating = True
End Sub
Sub UnhideStage6()
'
' UnhideStage6 Macro
'


'
    Application.ScreenUpdating = False
    Columns("N:N").Select
    Selection.EntireColumn.Hidden = False
    Sheets("Fee estimate").Select
    Rows("795:891").Select
    Selection.EntireRow.Hidden = False
    Sheets("CAWS codes").Select
    Range("D11").Select
    Application.ScreenUpdating = True
End Sub
Sub HIDEStage7()
'
' HIDEStage7 Macro
'


'
    Application.ScreenUpdating = False
    Columns("O:O").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Fee estimate").Select
    Rows("892:987").Select
    Selection.EntireRow.Hidden = True
    Sheets("CAWS codes").Select
    Range("D12").Select
    Application.ScreenUpdating = True
End Sub
Sub UnhideStage7()
'
' UnhideStage7 Macro
'


'
    Application.ScreenUpdating = False
    Columns("O:O").Select
    Selection.EntireColumn.Hidden = False
    Sheets("Fee estimate").Select
    Rows("892:987").Select
    Selection.EntireRow.Hidden = False
    Sheets("CAWS codes").Select
    Range("D12").Select
    Application.ScreenUpdating = True
End Sub


So my question is, is there an easier way of doing this? rather than having a thousand "If Target.Address(True, True) = "$D$5" Then" commands to look at all the individual cells.

Please let me know if this is unclear, I wanted to upload screenshots but couldn't work out how :confused:

Thanks
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Assuming your first two tables are in ranges A1:B5 and D1:G10 on the first sheet this should work for the first part:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B2:B5")) Is Nothing Then
        If Target.Value = "Yes" Then
            Columns(Target.Row + 4).Hidden = False
        ElseIf Target.Value = "No" Then
            Columns(Target.Row + 4).Hidden = True
        End If
    End If


End Sub
I'll look at the next part in a bit but the theory should be similar if you want to have a play.

Dom
 

LukusCarnilious

New Member
Joined
Jul 22, 2015
Messages
7
Thanks for your quick response!

It works fine once I've changed it for the tables actual locations.

What I also need that process to do, for example is if the stage 1 column is hidden on table 2 then in the third table (which is on another tab) all the rows which are to do with stage 1 need to be hidden too.

So if someone only needs stages 2 and 4 for example they can then go through and select the activities they do and don't need on table 2 which will then hide or unhide the relevant activity rows in table 3.

Thanks again for your swift reply!

Luke
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
There was a small error in the code above so it was hiding the wrong columns which I've fixed in this. It assumes your 3rd table is on Sheet2 in the range A1:B41.

Full code which should do what you ask:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B2:B5")) Is Nothing Then
        If Target.Value = "Yes" Then
            Columns(Target.Row + 3).Hidden = False
            Sheets("Sheet2").Range("B2:B11").Offset((Target.Row - 2) * 10, 0).EntireRow.Hidden = False
        ElseIf Target.Value = "No" Then
            Columns(Target.Row + 3).Hidden = True
            Sheets("Sheet2").Range("B2:B11").Offset((Target.Row - 2) * 10, 0).EntireRow.Hidden = True
        End If
    End If
    
    If Not Intersect(Target, Range("E2:H10")) Is Nothing Then
        If Target.Value = "Yes" Then
            Sheets("Sheet2").Rows(Target.Row + 1 + ((Target.Column - 5) * 10)).Hidden = False
        ElseIf Target.Value = "No" Then
            Sheets("Sheet2").Rows(Target.Row + 1 + ((Target.Column - 5) * 10)).Hidden = True
        End If
    End If
    
End Sub
Hope it helps,

Dom
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
You'll need to adjust the numbers to reflect tables being in different positions.

Dom
 

LukusCarnilious

New Member
Joined
Jul 22, 2015
Messages
7
Thanks,

I've made it work for the most part but I'm struggling with the offset to remove all the rows on table 3, and the removing of individual rows using table 2.

Basically on my actual tables I have the following

Table 1 - 10 Stages D3:D12

Table 2 - 10 Stage columns with 94 Activity rows cell range: F19:O112

Table 3 - 10 Stages with 97 rows per stage and the 94 activities within those 97 rows for every stage.
Stage 1 starts at row 19
Stage 2 therefore starts at row 116 and so on for the remaining stages

If you could help me change the code to account for the differences that would be much appreciated.

I used you code to set up the example above in a new worksheet so I know it works exactly how I need, but changing the values around for my sheet was not as simple as I thought, although I'm sure you will show me it is!

Thanks again for your help!

Luke
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
What's the exact layout of the 3rd table then and what range does it cover in total?

Is it:

Stage 1
Blank Row
Activity 1
Activity 2
Activity 3
Activity 4
...
Activity 94
Blank Row
Stage 2
Blank Row
Activity 1
Activity 2
Activity 3
Activity 4
...
Activity 94
Blank Row
Stage 3
etc...
 

LukusCarnilious

New Member
Joined
Jul 22, 2015
Messages
7
It covers the following range B18:O988

Row 18 is blank
Then it follows the layout you describe.

In terms of columns there are 14, the list you have written is in column C. Then the actual data which will be inputted by the user for each activity starts on column F, not sure if that helps?

Thanks

Luke
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
This should do it...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("E4:E13")) Is Nothing Then
        If Target.Value = "Yes" Then
            Columns(Target.Row + 2).Hidden = False
            Sheets("Sheet2").Range("C19:C115").Offset((Target.Row - 4) * 97, 0).EntireRow.Hidden = False
        ElseIf Target.Value = "No" Then
            Columns(Target.Row + 2).Hidden = True
            Sheets("Sheet2").Range("C19:C115").Offset((Target.Row - 4) * 97, 0).EntireRow.Hidden = True
        End If
    End If
    
    If Not Intersect(Target, Range("F19:O112")) Is Nothing Then
        If Target.Value = "Yes" Then
            Sheets("Sheet2").Rows(Target.Row + 2 + ((Target.Column - 6) * 97)).Hidden = False
        ElseIf Target.Value = "No" Then
            Sheets("Sheet2").Rows(Target.Row + 2 + ((Target.Column - 6) * 97)).Hidden = True
        End If
    End If
    
End Sub
 

Forum statistics

Threads
1,081,989
Messages
5,362,581
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top