Hiding Rows with VBA Question

K

Kevin_W

New Member
Joined
Aug 23, 2018
Messages
13
I have a worksheet that has several forms on it. Based on a cell selection I want to be able to hide and unhide various rows. I have been able to successfully do this. Now I would like to duplicate the form below the first form and create a separate selected cell to hide/unhide rows for that form. I think I have a syntax issue and am way to green in VBA to figure it out it seems. My code is inserted from right-clicking on the tab and inserting my code. My code works fine based on my C1 selection. I want the to repeat the code for C40.

What am I doing wrong?


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$C$1" Then
        Dim ITU As String
    
    LR = Cells(Rows.Count, "C").End(xlUp).Row
    ITU = Range("C1").Value
    Rows.EntireRow.Hidden = False
    
    If ITU = "12" Then
        Rows("6:6").EntireRow.Hidden = True
        Rows("22:22").EntireRow.Hidden = True
        Rows("8:8").EntireRow.Hidden = True
        Rows("24:24").EntireRow.Hidden = True
        Rows("17:17").EntireRow.Hidden = True
        Rows("33:33").EntireRow.Hidden = True
        Rows("20:20").EntireRow.Hidden = True
        Rows("36:36").EntireRow.Hidden = True
    ElseIf ITU = "8" Then
        Rows("6:6").EntireRow.Hidden = True
        Rows("22:22").EntireRow.Hidden = True
        Rows("8:9").EntireRow.Hidden = True
        Rows("24:25").EntireRow.Hidden = True
        Rows("12:12").EntireRow.Hidden = True
        Rows("28:28").EntireRow.Hidden = True
        Rows("17:20").EntireRow.Hidden = True
        Rows("33:36").EntireRow.Hidden = True
    Else
        Rows.EntireRow.Hidden = False
    End If
End If


If Target.Address = "$C$40" Then
        Dim ITU2 As String
    
    LR = Cells(Rows.Count, "C").End(xlUp).Row
    ITU2 = Range("C40").Value
    Rows.EntireRow.Hidden = False
    
    If ITU2 = "12" Then
        Rows("45:45").EntireRow.Hidden = True
        Rows("47:47").EntireRow.Hidden = True
        Rows("56:56").EntireRow.Hidden = True
        Rows("59:59").EntireRow.Hidden = True
        Rows("61:61").EntireRow.Hidden = True
        Rows("63:63").EntireRow.Hidden = True
        Rows("72:72").EntireRow.Hidden = True
        Rows("75:75").EntireRow.Hidden = True
    ElseIf ITU2 = "8" Then
        Rows("45:45").EntireRow.Hidden = True
        Rows("47:48").EntireRow.Hidden = True
        Rows("51:51").EntireRow.Hidden = True
        Rows("55:59").EntireRow.Hidden = True
        Rows("61:61").EntireRow.Hidden = True
        Rows("63:64").EntireRow.Hidden = True
        Rows("67:67").EntireRow.Hidden = True
        Rows("71:75").EntireRow.Hidden = True
    Else
        Rows.EntireRow.Hidden = False
    End If
End If


End Sub
 
Last edited by a moderator:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
In what way isn't your code working?
 
Upvote 0
Try:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C1,C40")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    If Target.Row = 1 Then
        Select Case Target.Value
            Case Is = 12
                Rows.EntireRow.Hidden = False
                Rows("6:6").EntireRow.Hidden = True
                Rows("22:22").EntireRow.Hidden = True
                Rows("8:8").EntireRow.Hidden = True
                Rows("24:24").EntireRow.Hidden = True
                Rows("17:17").EntireRow.Hidden = True
                Rows("33:33").EntireRow.Hidden = True
                Rows("20:20").EntireRow.Hidden = True
                Rows("36:36").EntireRow.Hidden = True
            Case Is = "8"
                Rows("6:6").EntireRow.Hidden = True
                Rows("22:22").EntireRow.Hidden = True
                Rows("8:9").EntireRow.Hidden = True
                Rows("24:25").EntireRow.Hidden = True
                Rows("12:12").EntireRow.Hidden = True
                Rows("28:28").EntireRow.Hidden = True
                Rows("17:20").EntireRow.Hidden = True
                Rows("33:36").EntireRow.Hidden = True
            Case Else
                Rows.EntireRow.Hidden = False
        End Select
    ElseIf Target.Row = 40 Then
        Select Case Target.Value
            Case Is = 12
                Rows.EntireRow.Hidden = False
                Rows("45:45").EntireRow.Hidden = True
                Rows("47:47").EntireRow.Hidden = True
                Rows("56:56").EntireRow.Hidden = True
                Rows("59:59").EntireRow.Hidden = True
                Rows("61:61").EntireRow.Hidden = True
                Rows("63:63").EntireRow.Hidden = True
                Rows("72:72").EntireRow.Hidden = True
                Rows("75:75").EntireRow.Hidden = True
            Case Is = "8"
                Rows.EntireRow.Hidden = False
                Rows("45:45").EntireRow.Hidden = True
                Rows("47:48").EntireRow.Hidden = True
                Rows("51:51").EntireRow.Hidden = True
                Rows("55:59").EntireRow.Hidden = True
                Rows("61:61").EntireRow.Hidden = True
                Rows("63:64").EntireRow.Hidden = True
                Rows("67:67").EntireRow.Hidden = True
                Rows("71:75").EntireRow.Hidden = True
            Case Else
                Rows.EntireRow.Hidden = False
        End Select
    End If
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
C1 and C40 have drop downs to select values of 8,12,16. If I select 12 in C1 I get the appropriate rows hidden. When I go to C40 and select 8, I get the appropriate rows hidden there but everything hidden from the C1 selection is then un-hidden. I want the actions in c1 and c40 to be independent.
 
Upvote 0
How about
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address = "$C$1" Then
      Rows("6:36").Hidden = False
      If Target.Value = "12" Then
         Rows("6:6").EntireRow.Hidden = True
         Rows("22:22").EntireRow.Hidden = True
         Rows("8:8").EntireRow.Hidden = True
         Rows("24:24").EntireRow.Hidden = True
         Rows("17:17").EntireRow.Hidden = True
         Rows("33:33").EntireRow.Hidden = True
         Rows("20:20").EntireRow.Hidden = True
         Rows("36:36").EntireRow.Hidden = True
      ElseIf Target.Value = "8" Then
         Rows("6:6").EntireRow.Hidden = True
         Rows("22:22").EntireRow.Hidden = True
         Rows("8:9").EntireRow.Hidden = True
         Rows("24:25").EntireRow.Hidden = True
         Rows("12:12").EntireRow.Hidden = True
         Rows("28:28").EntireRow.Hidden = True
         Rows("17:20").EntireRow.Hidden = True
         Rows("33:36").EntireRow.Hidden = True
      End If
   ElseIf Target.Address = "$C$40" Then
      Rows("45:75").Hidden = False
      If Target.Value = "12" Then
          Rows("45:45").EntireRow.Hidden = True
          Rows("47:47").EntireRow.Hidden = True
          Rows("56:56").EntireRow.Hidden = True
          Rows("59:59").EntireRow.Hidden = True
          Rows("61:61").EntireRow.Hidden = True
          Rows("63:63").EntireRow.Hidden = True
          Rows("72:72").EntireRow.Hidden = True
          Rows("75:75").EntireRow.Hidden = True
      ElseIf Target.Value = "8" Then
          Rows("45:45").EntireRow.Hidden = True
          Rows("47:48").EntireRow.Hidden = True
          Rows("51:51").EntireRow.Hidden = True
          Rows("55:59").EntireRow.Hidden = True
          Rows("61:61").EntireRow.Hidden = True
          Rows("63:64").EntireRow.Hidden = True
          Rows("67:67").EntireRow.Hidden = True
          Rows("71:75").EntireRow.Hidden = True
      End If
   End If
End Sub
 
Last edited:
Upvote 0
Sorry...somewhat messed up my last response. Mumps, what you sent works but I still get the same interaction between the sections. Fluff...your code did not really work at all (not complaining!)

Really appreciate the quick responses and help.
 
Upvote 0
My apologies to you Fluff! It might help if I copied the entire code you provided. It seems to work just as I wanted. I need to play with it for a few to really make sure it is good, but wanted to get this response out quickly.

Much appreciated. Now I need to study just what you provided :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Hide rows according to checkboxes on seperate sheet
Replies
8
Views
632
John_w
J
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
803
Peter_SSs
Peter_SSs
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
480
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
Hide Rows based on cell with drop down values
Replies
1
Views
1K
kevin9999
K

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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