Hide and Unhide rows based on two values in two columns

S

Satheesh9012

New Member
Joined
Mar 19, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have a doubt i have two columns where in based on the input the rows need to be hide and unhide,

So if row 8 with item type is selected with group id, and row 9 with item id group item row 19 should be visible, like wise row 8 has 7 options for hiding and unhiding rows

Please help
 
Sort by date Sort by votes
Satheesh9012 said:
Hi all,

I have a doubt i have two columns where in based on the input the rows need to be hide and unhide,

So if row 8 with item type is selected with group id, and row 9 with item id group item row 19 should be visible, like wise row 8 has 7 options for hiding and unhiding rows

Please help
Click to expand...
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("Issue type").Value = "Item1" And Range("Product").Value = "Soap" Then
            Rows(8).Hidden = False
            Rows(21).Hidden = False
            Rows(19).Hidden = False
            Rows("9:18").Hidden = True
            Rows(20).Hidden = True
If Range("Issue type").Value = "Item1" And Range("Product 2").Value = "Box" Then
            Rows(8).Hidden = False
            Rows(23).Hidden = False
            Rows(19).Hidden = False
            Rows("9:18").Hidden = True
            Rows(20).Hidden = True
        ElseIf Target.Value = "Item 2" Then
            Rows(10).Hidden = False
            Rows(16).Hidden = False
            Rows(21).Hidden = False
            Rows(9).Hidden = False
            Rows(15).Hidden = False
            Rows("11:14").Hidden = True
            Rows("17:20").Hidden = True
        'ElseIf Target.Value = "Item 3" Then
        'Rows("9:21").Hidden = False
        'Rows("19:20").Hidden = True
        'Rows(8).Hidden = True
        ElseIf Target.Value = "Item 4" Then
        Rows("8:19").Hidden = True
        Rows("20:21").Hidden = False
        Rows(15).Hidden = False
        ElseIf Target.Value = "Item 5" Then

End if
End Sub


I have used the above code but it does not work if the second column is selected
 
Upvote 0
Hi All

Please help i have values in column 8 and column 9, where in if column 8 issue type is selected with item1 column 9 will be visible and column 9 has three values in it so if column 9 (product) is selected with soap rows 21 should be visible, like vise there are three other values in column 9 as well for which rows should hide and unhide based on the selection, i have tried the below code so only column 8 works.

also note column 8 has 8 other values based on which rows are hidden and unhidden which is working fine for me.

please help when there is column 8 with issue type selected with item 1 column 9 unhides and column 9 has three other options where in the rows needs to be also hidden and unhidden


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("Issue type").Value = "Item1" And Range("Product").Value = "Soap" Then
Rows(8).Hidden = False
Rows(21).Hidden = False
Rows(19).Hidden = False
Rows("9:18").Hidden = True
Rows(20).Hidden = True
If Range("Issue type").Value = "Item1" And Range("Product 2").Value = "Box" Then
Rows(8).Hidden = False
Rows(23).Hidden = False
Rows(19).Hidden = False
Rows("9:18").Hidden = True
Rows(20).Hidden = True
ElseIf Target.Value = "Item 2" Then
Rows(10).Hidden = False
Rows(16).Hidden = False
Rows(21).Hidden = False
Rows(9).Hidden = False
Rows(15).Hidden = False
Rows("11:14").Hidden = True
Rows("17:20").Hidden = True
'ElseIf Target.Value = "Item 3" Then
'Rows("9:21").Hidden = False
'Rows("19:20").Hidden = True
'Rows(8).Hidden = True
ElseIf Target.Value = "Item 4" Then
Rows("8:19").Hidden = True
Rows("20:21").Hidden = False
Rows(15).Hidden = False
ElseIf Target.Value = "Item 5" Then

End if
End Sub
 
Upvote 0
Satheesh9012 said:
Hi All

Please help i have values in column 8 and column 9, where in if column 8 issue type is selected with item1 column 9 will be visible and column 9 has three values in it so if column 9 (product) is selected with soap rows 21 should be visible, like vise there are three other values in column 9 as well for which rows should hide and unhide based on the selection, i have tried the below code so only column 8 works.

also note column 8 has 8 other values based on which rows are hidden and unhidden which is working fine for me.

please help when there is column 8 with issue type selected with item 1 column 9 unhides and column 9 has three other options where in the rows needs to be also hidden and unhidden


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("Issue type").Value = "Item1" And Range("Product").Value = "Soap" Then
Rows(8).Hidden = False
Rows(21).Hidden = False
Rows(19).Hidden = False
Rows("9:18").Hidden = True
Rows(20).Hidden = True
If Range("Issue type").Value = "Item1" And Range("Product 2").Value = "Box" Then
Rows(8).Hidden = False
Rows(23).Hidden = False
Rows(19).Hidden = False
Rows("9:18").Hidden = True
Rows(20).Hidden = True
ElseIf Target.Value = "Item 2" Then
Rows(10).Hidden = False
Rows(16).Hidden = False
Rows(21).Hidden = False
Rows(9).Hidden = False
Rows(15).Hidden = False
Rows("11:14").Hidden = True
Rows("17:20").Hidden = True
'ElseIf Target.Value = "Item 3" Then
'Rows("9:21").Hidden = False
'Rows("19:20").Hidden = True
'Rows(8).Hidden = True
ElseIf Target.Value = "Item 4" Then
Rows("8:19").Hidden = True
Rows("20:21").Hidden = False
Rows(15).Hidden = False
ElseIf Target.Value = "Item 5" Then

End if
End Sub
Click to expand...
@Fluff
Could you please help with this
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Rows to hide and unhide based on two columns
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Solved
Hide all worksheets that don't contain the string value of the active cell.
Replies
2
Views
88
Peter_SSs
Peter_SSs
F
  • Solved
VBA to Automatically Hide/Unhide Dynamic Range based Upon Cell Value
Replies
3
Views
591
franswa3434
F
N
  • Question Question
Toggle code to hide and unhide columns based on text in a row
Replies
1
Views
608
Cubist
Cubist
M
  • Solved
VBA Hide/Unhide Columns, different/multiple sheets
2 3
Replies
20
Views
972
Minx_Minxy
M
B
  • Question Question
Automatically hide/unhide rows
Replies
6
Views
173
kevin9999
K

Share this page

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