VBE - List Macro (Hide/Unhide rows)

Tingle

New Member
Joined
Dec 21, 2016
Messages
47
Hi all,

Can anyone help me solve this problem... I have a drop down list with 'Yes & No' as the options. By selecting 'Yes' it can either hide or unhide certain rows on the the same sheet. Same for selecting No.

A simple 'Record Macro' does not work.

Will the same formula work for a list with 8 options on the list?

Thanks in advance.

Tingle
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Will the same formula work for a list with 8 options on the list?
Should be able to handle that.

Please provide the pertinent details, such as:
- What cell is this drop-down box in?
- Which rows you want hidden in which case?
- If some rows are already hidden, should those ones by unhidden before applying the new selection?
 
Upvote 0
Ok the first drop down box will be in cell D:13 (List) - Yes or No

- If Yes is selected then hide row 28-55
- If no is selected no change. (Or it will hide row 28-55. The reason I say that is because someone may select Yes by accident then not know how to hide the rows again.

The bigger list...

D13

List - Type 1, Type 2, Type 3 & Type 4

Type 1 hides row 22 & 23
Type 2 hides row 24 & 25
Type 3 hides row 26 & 27
Type 4 hides row 28 & 29

But again if the user selects 1 then changes his mind to 4 I would like just the rows for type 4 to be viewable.

Hope that helps :)
 
Upvote 0
For the bigger list,

I could hide all the data, then I would just need the 'Type 1' to then trigger a unhide action.

Let me know what you think.
 
Upvote 0
We can create a Worksheet_Event procedure that fires automatically whenever that value in D13 changes.

So, right-click on the sheet tab name at the bottom of the sheet, select View Code, and paste this VBA code in the resulting VB Editor window:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run if cell D13 is updated
    If Target.Address = Range("D13").Address Then
'       Unhide all rows to begin
        Rows("22:29").Hidden = False
'       Hide rows based on selection
        Select Case Target.Value
            Case "Type 1"
                Rows("22:23").Hidden = True
            Case "Type 2"
                Rows("24:25").Hidden = True
            Case "Type 3"
                Rows("26:27").Hidden = True
            Case "Type 4"
                Rows("28:29").Hidden = True
        End Select
    End If
    
End Sub
Now try changing the selections in D13 and see what happens!
 
Upvote 0
Brilliant thanks!

If I wanted the data to start off hidden, but instead of row 22:29 I just wanted row 7, 8, 15, 16 & 23. Would I write hide all rows to begin, then unhide rows based on selection?

Is is that right?
 
Upvote 0
Private Sub Worksheet_Change(ByVal Target As Range)
' Only run if cell D13 is updated
If Target.Address = Range("D13").Address Then
' hide all rows to begin
Rows("7:8").Hidden = True
Rows("15:16").Hidden = True
Rows("23").Hidden = True
' unhide rows based on selection
Select Case Target.Value
Case "Type 1"
Rows("7:8").Hidden = False
Case "Type 1"
Rows("15:16").Hidden = False
Case "Type 1"
Rows("23").Hidden = False
End Select
End If

End Sub
 
Upvote 0
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'   Only run if cell D13 is updated
    If Target.Address = Range("D13").Address Then
'       hide all rows to begin
        Rows("7:8").Hidden = True
        Rows("15:16").Hidden = True
        Rows("23").Hidden = True
'       unhide rows based on selection
        Select Case Target.Value
            Case "Type 1"
                Rows("7:8").Hidden = False
                Rows("15:16").Hidden = False
                Rows("23").Hidden = False
        End Select
'   Only run if cell D23 is updated
        If Target.Address = Range("D23").Address Then
'       hide all rows to begin
        Rows("26:53").Hidden = True
'       unhide rows based on selection
        Select Case Target.Value
            Case "Yes"
                Rows("26:53").Hidden = False
    End Select
    
End Sub
 
Upvote 0
Can anyone help me with above?

Out of a list of 10 options, I need case "type 1" & case "type 2" to unhide the rows (7,8,15,16 & 23), then by selecting D23 (this row would be hidden if case 1 or 2 is not selected) "Yes" it will then unhide rows "23:53". If no is selected. the rows stay hidden.

Thanks
:)
 
Upvote 0
Red is new
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
'   Only run if cell D13 is updated
    If Target.Address = Range("D13").Address Then
'       hide all rows to begin
        Rows("7:8").Hidden = True
        Rows("15:16").Hidden = True
        Rows("23").Hidden = True
'       unhide rows based on selection
        Select Case Target.Value
'       unhide rows 7, 8, 15, 16
            Case "Type 1"
                Rows("7:8").Hidden = False
                Rows("15:16").Hidden = False
'       unhide rows 7, 8, 15, 16,23
            Case "Type 2"
                Rows("7:8").Hidden = False
                Rows("15:16").Hidden = False
                Rows("23").Hidden = False
            Else
'       if nothing selected -> ask user to do it:)
                MsgBox ("please, choose option from list!")
        End Select
        end if
'   Only run if cell D23 is updated
        If Target.Address = Range("D23").Address Then
'       hide rows 26-53 to begin
        Rows("26:53").Hidden = True
'       unhide rows based on selection
        Select Case Target.Value
            Case "Yes"
                Rows("26:53").Hidden = False
            Case "No"
'       nothing happens?)
'                Rows("26:53").Hidden = False
            Else
                MsgBox ("Choose yes or no")
    End Select
    End If
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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