VBA - Hiding and Unhiding numerous rows based on numerous cells

L

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:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Stages[/TD]
[TD]Yes or No[/TD]
[/TR]
[TR]
[TD]Stage 1[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Stage 2[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Stage 3[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Stage 4[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]


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


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Stage 1[/TD]
[TD]Stage 2[/TD]
[TD]Stage 3[/TD]
[TD]Stage 4[/TD]
[/TR]
[TR]
[TD]Activity 1[/TD]
[TD]Yes[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Activity 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Activity 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.














[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Activity and stage[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]Stage 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Activity 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Activity 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Activity 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Activity 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stage 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Activity 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Activity 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Activity 3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You'll need to adjust the numbers to reflect tables being in different positions.

Dom
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA Hide/Unhide Columns, different/multiple sheets
2 3
Replies
20
Views
361
Minx_Minxy
M
C
  • Solved
Hide Columns
Replies
9
Views
151
Joe4
Joe4
D
  • Question Question
VBA help -- hide columns based upon drop down
Replies
9
Views
372
SunnyAlv
SunnyAlv
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
406
Joe4
Joe4
N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
99
Northern NY Design
N

Forum statistics

Threads
1,223,445
Messages
6,172,176
Members
452,446
Latest member
walkman99

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top