VBA: Hide rows based on column A

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 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
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>
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,555
Office Version
  1. 2013
Platform
  1. 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,245
Office Version
  1. 2016
Platform
  1. 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
17,555
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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,245
Office Version
  1. 2016
Platform
  1. 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
17,555
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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,245
Office Version
  1. 2016
Platform
  1. 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
17,555
Office Version
  1. 2013
Platform
  1. 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,129,332
Messages
5,635,670
Members
416,871
Latest member
jbcpub

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
Top