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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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

D
  • Question
VBA code to hide/unhide based on drop-down box and tick boxes
Replies
0
Views
360
DaveOli
D
PuntingJawa
  • Solved
Macro to hide/unhide rows based on cell value
2
Replies
11
Views
172
PuntingJawa
PuntingJawa
S
  • Question
Hide / unhide columns without using macros / VBA
Replies
1
Views
155
JEC
JEC
N
  • Question
Hiding rows based on cell value
Replies
2
Views
97
smozgur
smozgur
B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
15
Views
247
mumps
mumps

Forum statistics

Threads
1,214,911
Messages
6,122,198
Members
449,072
Latest member
DW Draft

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