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:
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
 
Sort by date Sort by votes

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
You must log in or register to reply here.

Similar threads

G
  • Solved
Macro - Columns Are not Hidding / unhiding correctly
Replies
4
Views
203
GarethCW
G
M
  • Question
VBA Macro - hides columns based on a single cell which is limited by data validation
Replies
3
Views
61
rlv01
R
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
638
kevin9999
K
D
  • Solved
VBA Conditional Statement That Functions Based on Today's Date
2
Replies
18
Views
289
Cubist
Cubist
A
  • Solved
VBA if cancelled don't print otherwise print
Replies
2
Views
159
afc171
A

Forum statistics

Threads
1,216,180
Messages
6,129,339
Members
449,504
Latest member
Alan the procrastinator

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