hide rows using VBA

M

Miskar

New Member
Joined
Apr 18, 2019
Messages
3
I have drop down lists in 3 cells, C3, C4, and C5 each has 1-4 to pick from. I have the descriptions in row 7:66. If 1 is chosen in C3, I want to hide 10:66, if 1 is chosen in C4 I want to show row 10 and hide 11:66 (if 2 is selected hide 12:66, 3 - 13:66, 4 - 14:66). Lastly if 1 is selected in the 3rd option I want to show 7:15 and hide every thing else. I would like this for every combination. I am able to hide the rows for the 1st drop down but can't get the rest. I am using then follwing code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C3")) Is Nothing Then
Rows("7:66").Hidden = True
Select Case Target.Value
Case Is = 1
Rows("7:21").Hidden = False
Case Is = 2
Rows("7:36").Hidden = False
Case Is = 3
Rows("7:51").Hidden = False
Case Is = 4
Rows("7:66").Hidden = False
Case Else
Rows("7:66").Hidden = False

End Select
End If
End Sub

Is it possible to hide rows that are not stacked on top of each other based on three different drop downs?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Your OP is confusing :confused:

Sorry, but your explanation is not clear and contradicts what is in your code

You say If 1 is chosen in C3, I want to hide 10:66 but your code says if C3 = 1 show 7:21 Hide 22:66
Code: 
 YOUR CODE:
Rows("[COLOR=#ff0000]7:66[/COLOR]").Hidden = True
Select Case Target.value
Case Is = 1: Rows("[COLOR=#ff0000]7:21[/COLOR]").Hidden = False

I would like this for every combination
Click to expand...
- what does that mean :confused:
- every combination of what?


I have drop down lists in 3 cells, C3, C4, and C5 each has 1-4 to pick from. I have the descriptions in row 7:66. If 1 is chosen in C3, I want to hide 10:66, if 1 is chosen in C4 I want to show row 10 and hide 11:66 (if 2 is selected hide 12:66, 3 - 13:66, 4 - 14:66). Lastly if 1 is selected in the 3rd option I want to show 7:15 and hide every thing else
Click to expand...
This achieves what is in the quote
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address(0, 0) = "C3" Then
        Rows("10:66").Hidden = True
        Select Case Target
            Case 1: Rows("10:21").Hidden = False
            Case 2: Rows("10:36").Hidden = False
            Case 3: Rows("10:51").Hidden = False
            Case Else: Rows("7:66").Hidden = False
        End Select
    End If
    If Target.Address(0, 0) = "C4" Then
        Rows("10:66").Hidden = True
        Select Case Target
            Case 1: Rows("10").Hidden = False
            Case 2: Rows("10:11").Hidden = False
            Case 3: Rows("10:12").Hidden = False
            Case 4: Rows("10:13").Hidden = False
        End Select
    End If
    If Target.Address(0, 0) = "C5" Then
        Rows("7:66").Hidden = True
        Select Case Target
            Case 1: Rows("7:15").Hidden = False
        End Select
    End If
End Sub


Note the changes made in the code to make it shorter and easier to read
Instead of using Intersect: If Target.Address(0, 0) = "C4" Then
Use of COLON & Case 1 instead of Case is 1 Case 1: Rows("10:21").Hidden = False
Select Case Target.Value is fine but .Value is the default so this also works here: Select Case Target

If the above is not what you want ...
If you need further help, please provide a simple list (no explanation) of what you want
- as below for every combination

C3 =1 Show 10:21 Hide 22:66
C3 =2 Show 10:36 Hide 37:66
etc
 
Last edited:
Upvote 0
If you need further help, please provide a simple list (no explanation) of what you want
- as below for every combination

C3 =1 Show 10:21 Hide 22:66
C3 =2 Show 10:36 Hide 37:66
etc
Thank You for your help this got me a lot closer. Below is a simple list. What I am trying to do may not be possible?
Rows associated with C3 = 7:9, 22:24, 37:39, 52:54
Rows associated with C4 = 10:13, 25:28, 40:43, 55:58
Rows associated with C5 = 14:21, 29:36, 44:51, 59:66
C3 = 1 show 7:9 Hide 22:24, 7:39, 52:54 and C4= 1 show 10 Hide 11:13, 25:28, 40:43, 55:58 and C5 = 1 show 14:15 hide 16:21, 29:36, 44:51, 59:66
C3 = 2 show 7:9 , 22:24 hide, 7:39, 52:54 and C4= 1 show 10 Hide 11:13, 25:28, 40:43, 55:58 and C5 = 1 show 14:15 hide 16:21, 29:36, 44:51, 59:66
C3 = 3 show 7:9 , 22:24, 7:39, hide 52:54 and C4= 1 show 10 Hide 11:13, 25:28, 40:43, 55:58 and C5 = 1 show 14:15 hide 16:21, 29:36, 44:51, 59:66
C3 = 4 show 7:9 , 22:24, 7:39, 52:54 and C4= 1 show 10 Hide 11:13, 25:28, 40:43, 55:58 and C5 = 1 show 14:15 hide 16:21, 29:36, 44:51, 59:66

C3 = 2 show 7:9 , 22:24 hide, 7:39, 52:54 and C4= 1 show 10 Hide 11:13, 25:28, 40:43, 55:58 and C5 = 2 show 14:17, hide, 18:21 29:36, 44:51, 59:66
C3 = 2 show 7:9 , 22:24 hide, 7:39, 52:54 and C4= 1 show 10 Hide 11:13, 25:28, 40:43, 55:58 and C5 = 3 show 14:19, hide, 20:21 29:36, 44:51, 59:66
C3 = 2 show 7:9 , 22:24 hide, 7:39, 52:54 and C4= 1 show 10 Hide 11:13, 25:28, 40:43, 55:58 and C5 = 4 show 14:21, hide, 29:36, 44:51, 59:66

- what does that mean :confused:
- every combination of what?

C3 = 1 and C4 = 1 and C5 = 1
C3 = 2 and C4 = 1 and C5 = 1
C3 = 3 and C4 = 1 and C5 = 1
C3 = 4 and C4 = 1 and C5 = 1
C3 = 2 and C4 = 2 and C5 = 1
C3 = 2 and C4 = 3 and C5 = 1
C3 = 2 and C4 = 4 and C5 = 1
C3 = 2 and C4 = 2 and C5 = 2
C3 = 2 and C4 = 2 and C5 = 3
C3 = 2 and C4 = 2 and C5 = 4
...
C3 = 4 and C4 = 4 and C5 = 4

 
Upvote 0
Is this what you want
Place both procedures in SHEET module
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range
    If Not Intersect(Range("C3:C5"), Target) Is Nothing Then HideUnhideRows
End Sub
Code: 
Private Sub HideUnhideRows()
    Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, cell As Range
    For Each cell In Range("C3:C5")
        Select Case cell.Address(0, 0)
            Case "C3"
            Set r1 = Rows("7:9"):    Set r2 = Rows("22:24"):  Set r3 = Rows("37:39"):  Set r4 = Rows("52:54")
            Case "C4"
            Set r1 = Rows("10:13"):  Set r2 = Rows("25:28"):  Set r3 = Rows("40:43"):  Set r4 = Rows("55:58")
            Case "C5"
            Set r1 = Rows("14:21"):  Set r2 = Rows("29:39"):  Set r3 = Rows("44:51"):  Set r4 = Rows("59:66")
        End Select
        
        Union(r1, r2, r3, r4).EntireRow.Hidden = True
        
        Select Case cell.Value
            Case 1:     r1.EntireRow.Hidden = False
            Case 2:     Union(r1, r2).EntireRow.Hidden = False
            Case 3:     Union(r1, r2, r3).EntireRow.Hidden = False
            Case Else:  Union(r1, r2, r3, r4).EntireRow.Hidden = False
        End Select
    Next cell
End Sub
 
Upvote 0
Thanks for all your help. I was able to get it using the following. I had to add a couple of rows with choices to get it. If I select "4" and change it to "2" in C3 it doesn't go back, other than that it works. Thanks again

Private Sub Worksheet_Change(ByVal Target As Range)


If Range("C3").Value = "" Then
Rows("4:11").EntireRow.Hidden = True
Rows("13:72").EntireRow.Hidden = True
ElseIf Range("C3").Value = "0" Then
Rows("4:11").EntireRow.Hidden = True
Rows("13:72").EntireRow.Hidden = True
ElseIf Range("C3").Value = "1" Then
Rows("4").EntireRow.Hidden = False
Rows("8").EntireRow.Hidden = False
Rows("13:15").EntireRow.Hidden = False
ElseIf Range("C3").Value = "2" Then
Rows("4:5").EntireRow.Hidden = False
Rows("8:9").EntireRow.Hidden = False
Rows("13:15").EntireRow.Hidden = False
Rows("28:30").EntireRow.Hidden = False
ElseIf Range("C3").Value = "3" Then
Rows("4:6").EntireRow.Hidden = False
Rows("8:10").EntireRow.Hidden = False
Rows("13:15").EntireRow.Hidden = False
Rows("28:30").EntireRow.Hidden = False
Rows("43:45").EntireRow.Hidden = False
ElseIf Range("C3").Value = "4" Then
Rows("4:7").EntireRow.Hidden = False
Rows("8:11").EntireRow.Hidden = False
Rows("13:15").EntireRow.Hidden = False
Rows("28:30").EntireRow.Hidden = False
Rows("43:45").EntireRow.Hidden = False
Rows("58:60").EntireRow.Hidden = False
End If

If Range("C4").Value = "0" Then
Rows("16:19").EntireRow.Hidden = True
ElseIf Range("C4").Value = "1" Then
Rows("16").EntireRow.Hidden = False
ElseIf Range("C4").Value = "2" Then
Rows("16:17").EntireRow.Hidden = False
ElseIf Range("C4").Value = "3" Then
Rows("16:18").EntireRow.Hidden = False
ElseIf Range("C4").Value = "4" Then
Rows("16:19").EntireRow.Hidden = False
End If


If Range("C8").Value = "0" Then
Rows("20:27").EntireRow.Hidden = True
ElseIf Range("C8").Value = "1" Then
Rows("20:21").EntireRow.Hidden = False
ElseIf Range("C8").Value = "2" Then
Rows("20:23").EntireRow.Hidden = False
ElseIf Range("C8").Value = "3" Then
Rows("20:25").EntireRow.Hidden = False
ElseIf Range("C8").Value = "4" Then
Rows("20:27").EntireRow.Hidden = False
End If

If Range("C5").Value = "0" Then
Rows("31:34").EntireRow.Hidden = True
ElseIf Range("C5").Value = "1" Then
Rows("31").EntireRow.Hidden = False
ElseIf Range("C5").Value = "2" Then
Rows("31:32").EntireRow.Hidden = False
ElseIf Range("C5").Value = "3" Then
Rows("31:33").EntireRow.Hidden = False
ElseIf Range("C5").Value = "4" Then
Rows("31:34").EntireRow.Hidden = False
End If

If Range("C9").Value = "0" Then
Rows("35:42").EntireRow.Hidden = True
ElseIf Range("C9").Value = "1" Then
Rows("35:36").EntireRow.Hidden = False
ElseIf Range("C9").Value = "2" Then
Rows("35:38").EntireRow.Hidden = False
ElseIf Range("C9").Value = "3" Then
Rows("35:40").EntireRow.Hidden = False
ElseIf Range("C9").Value = "4" Then
Rows("35:42").EntireRow.Hidden = False
End If

If Range("C6").Value = "0" Then
Rows("46:49").EntireRow.Hidden = True
ElseIf Range("C6").Value = "1" Then
Rows("46").EntireRow.Hidden = False
ElseIf Range("C6").Value = "2" Then
Rows("46:47").EntireRow.Hidden = False
ElseIf Range("C6").Value = "3" Then
Rows("46:48").EntireRow.Hidden = False
ElseIf Range("C6").Value = "4" Then
Rows("46:49").EntireRow.Hidden = False
End If

If Range("C10").Value = "0" Then
Rows("50:57").EntireRow.Hidden = True
ElseIf Range("C10").Value = "1" Then
Rows("50:51").EntireRow.Hidden = False
ElseIf Range("C10").Value = "2" Then
Rows("50:53").EntireRow.Hidden = False
ElseIf Range("C10").Value = "3" Then
Rows("50:55").EntireRow.Hidden = False
ElseIf Range("C10").Value = "4" Then
Rows("50:57").EntireRow.Hidden = False
End If



If Range("C7").Value = "0" Then
Rows("61:64").EntireRow.Hidden = True
ElseIf Range("C7").Value = "1" Then
Rows("61").EntireRow.Hidden = False
ElseIf Range("C7").Value = "2" Then
Rows("61:62").EntireRow.Hidden = False
ElseIf Range("C7").Value = "3" Then
Rows("61:63").EntireRow.Hidden = False
ElseIf Range("C7").Value = "4" Then
Rows("61:64").EntireRow.Hidden = False
End If

If Range("C11").Value = "0" Then
Rows("65:72").EntireRow.Hidden = True
ElseIf Range("C11").Value = "1" Then
Rows("65:66").EntireRow.Hidden = False
ElseIf Range("C11").Value = "2" Then
Rows("65:68").EntireRow.Hidden = False
ElseIf Range("C11").Value = "3" Then
Rows("65:70").EntireRow.Hidden = False
ElseIf Range("C11").Value = "4" Then
Rows("65:72").EntireRow.Hidden = False
End If










End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
Somewhat basic UDFs work except that they return #VALUE! error after hiding rows with VBA.
Replies
2
Views
129
OaklandJim
O
E
  • Solved
Quick VBA code to Hide Rows based on Cell Value
Replies
3
Views
611
Joe4
Joe4
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
1K
kevin9999
K
M
  • Question Question
VBA Macro - hides rows based on a single cell which is limited by data validation
Replies
6
Views
372
My Aswer Is This
My Aswer Is This
M
  • Question Question
VBA Macro - hides columns based on a single cell which is limited by data validation
Replies
4
Views
355
macca_18380
M

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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