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

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
378
Minx_Minxy
M
C
  • Solved
Hide Columns
Replies
9
Views
152
Joe4
Joe4
D
  • Question Question
VBA help -- hide columns based upon drop down
Replies
9
Views
373
SunnyAlv
SunnyAlv
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
418
Joe4
Joe4
N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
101
Northern NY Design
N

Forum statistics

Threads
1,223,532
Messages
6,172,875
Members
452,486
Latest member
standw01

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