VBA: Hide rows based on column A

[FryGirl]

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.

[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

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Out[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

_{^{<strike>
</strike>}}

 

[FryGirl]

Any thoughts on this?

 

[My Aswer Is This]

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]

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]

Try this:

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]

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.

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

 

[My Aswer Is This]

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​

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]

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]

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.