Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Hiding Rows with VBA Question

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Hiding Rows with VBA Question

    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 Fluff; Aug 23rd, 2018 at 10:37 AM. Reason: Code tags

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,484
    Post Thanks / Like
    Mentioned
    249 Post(s)
    Tagged
    17 Thread(s)

    Default Re: Hiding Rows with VBA Question

    In what way isn't your code working?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    6,313
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Hiding Rows with VBA Question

    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    New Member
    Join Date
    Aug 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hiding Rows with VBA Question

    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.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    13,484
    Post Thanks / Like
    Mentioned
    249 Post(s)
    Tagged
    17 Thread(s)

    Default Re: Hiding Rows with VBA Question

    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 by Fluff; Aug 23rd, 2018 at 11:00 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  6. #6
    New Member
    Join Date
    Aug 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hiding Rows with VBA Question

    Thanks....but it still gives me the interactions between the 2 forms

  7. #7
    New Member
    Join Date
    Aug 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hiding Rows with VBA Question

    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.

  8. #8
    New Member
    Join Date
    Aug 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hiding Rows with VBA Question

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •