VBA: Hide rows based on column A

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,159
Office Version
2016
Platform
Windows
This code works fine, but as I need to adjust my sheet rows and which ones should be visible and/or hidden, I would like to put a number in column A to represent the grouping.

Code:
[FONT=Verdana]Private Sub Worksheet_Change(ByVal Target As Range)
    Me.Cells.EntireRow.Hidden = False
    If Not Intersect(Target, Me.Range("B2")) Is Nothing Then
        If Target.Value = "In" Then
            Me.Rows("7:9").EntireRow.Hidden = True
        ElseIf Target.Value = "Out" Then
            Me.Rows("11:15").EntireRow.Hidden = True
        End If
    End If
End Sub
[/FONT]
Example, all of the "In" would have a 1 in column A on that row and "Out" would have a 2. So instead of manually changing the code to represent the group of rows, can I use another method to build the strings? Hope this makes sense.

Excel 2016 (Windows) 64 bit
AB
1
2Out
3
4
5
6
71
81
91
10
112
122
132
142
152
16
17
18
19
20

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

<strike>
</strike>
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
I read this question but was not sure about the numbers in column A

If we unhide the rows do the 1's and 2's need to be removed?
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,159
Office Version
2016
Platform
Windows
No, I was planning on using the numbers in column A to identify what rows would need to be hidden, so therefore, they would stay.

If my range of rows should happen to expand or contract, I will number those rows and then when the macro is run it will no know the new ranges.

I hope that makes sense.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Try this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   'Modified  10/17/2019  4:52:50 PM  EDT
    Me.Cells.EntireRow.Hidden = False
    
    If Not Intersect(Target, Me.Range("B2")) Is Nothing Then
  Application.EnableEvents = False
    
        If Target.Value = "In" Then
            Me.Rows("7:9").EntireRow.Hidden = True
            Cells(7, 1).Resize(3).Value = 1
        ElseIf Target.Value = "Out" Then
            Me.Rows("11:15").EntireRow.Hidden = True
            Cells(11, 1).Resize(5).Value = 2
        End If
    End If
Application.EnableEvents = True
End Sub
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,159
Office Version
2016
Platform
Windows
Not sure this is going to work as it hard codes the rows(7:9) and rows(11:15).

I'm sorry, I have explained this horribly. The numbers in column A will be placed manually by the user and in sequential order. Thinking about it more, I will like a macro to create a named range of each grouping of numbers, 1 and 2.

Now when the macro runs "on change", the named ranges are created and based on the selection from B2, that group of rows is hidden. I hope this explains it better.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Me.Cells.EntireRow.Hidden = False
    If Not Intersect(Target, Me.Range("B2")) Is Nothing Then
        If Target.Value = "In" Then
            Me.Rows("[COLOR=#ff0000]FirstGroup[/COLOR]").EntireRow.Hidden = True
        ElseIf Target.Value = "Out" Then
            Me.Rows("[COLOR=#ff0000]SecondGroup[/COLOR]").EntireRow.Hidden = True
        End If
    End If
End Sub
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
I like it when users tell me their ultimate goal and let me suggest how it should be done

If I understand your question correctly then here is what you want.

If Range("B2") value="In" and if any cell in column A has a 1 then Hide that row.

If Range("B2") value="Out" and if any cell in column A has a 2 then Hide that row.

And the script starts looking in row(3)

If all that is true then try this script
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
 'Modified  10/19/2019  11:26:56 AM  EDT
    If Not Intersect(Target, Me.Range("B2")) Is Nothing Then
    Me.Cells.EntireRow.Hidden = False
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
            If Target.Value = "In" Then
        
                For i = 3 To Lastrow
                    If Cells(i, 1).Value = 1 Then Rows(i).Hidden = True
                Next
            End If
        
        If Target.Value = "Out" Then
        
                For i = 3 To Lastrow
                    If Cells(i, 1).Value = 2 Then Rows(i).Hidden = True
                Next
            End If
    End If
        End Sub
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,159
Office Version
2016
Platform
Windows
Yes, thank you so much. I had to Dim i as long since Option Explicit is at the top of the sheet module, but outside of that, it worked great.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
Yes, thank you so much. I had to Dim i as long since Option Explicit is at the top of the sheet module, but outside of that, it worked great.
Oh. I guess I forgot to Dim i
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,771
Messages
5,470,690
Members
406,717
Latest member
Harsha Maskara

This Week's Hot Topics

Top