VBA: Hide rows based on column A

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,368
Office Version
  1. 365
  2. 2016
Platform
  1. 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

<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>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,815
Messages
6,181,136
Members
453,021
Latest member
Justyna P

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