Hiding Rows based on multiple cell inputs

D

DavidWT87

New Member
Joined
May 10, 2023
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Hello

I am creating a excel template at work for testing procedures. The idea is this will be a global testing template, which will contain all tests we could ever want to carry out, with a user selecting various project specific criteria, that would then hide rows as required to filter out unnecessary tests, leaving only what is required for the specific project. The problem is I am a total novice when it comes to using VB code, and it isn't doing what I want - hopefully I can get some assistance.

Done some online searches etc and have the following code for one menu choice. If a user selects 35 from the drop down, it will remove rows 25 to 70, but if they select 70, it will remove 71 to 116.

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("F14") = "35" Then
Row("25:70").EntireRow.Hidden = True
Else
Row("25:70").EntireRow.Hidden = False
End If

If Range("F14") = "70" Then
Row("71:116").EntireRow.Hidden = True
Else
Row("71:116").EntireRow.Hidden = False
End If

End Sub

This seems to work fine, and does exactly what I need. But I'm running into issues when trying to add new rules that will remove subsets. I'm guessing they are starting to conflict with each other, and rows aren't unhiding, rows that have no code associated with them are hiding and all kinds of weird things are happening, and as a novice I have no clue how to make it do what I want!

The next thing I want to do is add the next rule - for example if a cell (lets say F15) = YES, then I want to hide rows 25-30 and 71-75. And then for example have a 3rd rule, whereby if cell F16 = YES, rows 40-50 and rows 86-96 are hidden.

Hopefully that's clear. I don't know if want I want to do to is even possible, but hopefully someone can help either way.

Cheers
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Hello @DavidWT87
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​


Try this:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  
  If Not Intersect(Target, Range("F14")) Is Nothing Then
    Range("25:70").EntireRow.Hidden = Range("F14") = "35"
    Range("71:116").EntireRow.Hidden = Range("F14") = "70"
  End If
  
  If Not Intersect(Target, Range("F15")) Is Nothing Then
    Range("25:30, 71:75").EntireRow.Hidden = Range("F15") = "YES"
  End If
  
  If Not Intersect(Target, Range("F16")) Is Nothing Then
    Range("40:50, 86:96").EntireRow.Hidden = Range("F16") = "YES"
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------
 
Upvote 0
DanteAmor said:
Hello @DavidWT87
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​


Try this:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
 
  If Not Intersect(Target, Range("F14")) Is Nothing Then
    Range("25:70").EntireRow.Hidden = Range("F14") = "35"
    Range("71:116").EntireRow.Hidden = Range("F14") = "70"
  End If
 
  If Not Intersect(Target, Range("F15")) Is Nothing Then
    Range("25:30, 71:75").EntireRow.Hidden = Range("F15") = "YES"
  End If
 
  If Not Intersect(Target, Range("F16")) Is Nothing Then
    Range("40:50, 86:96").EntireRow.Hidden = Range("F16") = "YES"
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------
Click to expand...
Thank you. I’m actually now off for the weekend (wanted to hopefully get some answers ready to attack it again next week), but will let you know if it works. Thanks again!
 
Upvote 0
DanteAmor said:
Hello @DavidWT87
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​


Try this:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
 
  If Not Intersect(Target, Range("F14")) Is Nothing Then
    Range("25:70").EntireRow.Hidden = Range("F14") = "35"
    Range("71:116").EntireRow.Hidden = Range("F14") = "70"
  End If
 
  If Not Intersect(Target, Range("F15")) Is Nothing Then
    Range("25:30, 71:75").EntireRow.Hidden = Range("F15") = "YES"
  End If
 
  If Not Intersect(Target, Range("F16")) Is Nothing Then
    Range("40:50, 86:96").EntireRow.Hidden = Range("F16") = "YES"
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------
Click to expand...

This seemed to work, thank you. Been playing about with it and I'm comfortable I can add in extra rules/conditions as I need to based on the above.

What I have noticed is that when I clear selections from my drop down boxes, it doesn't unhide the rows associated with that rule. Is it possible to do this?

The other thing I have noticed is that changing a drop down doesn't always take everything with it. For example, I select 35, and it hides rows 25-70, and then select YES to my other 2 rules, and it removes rows 71-75 and 86-96 as I want it to.

But say I then need to change my initial selection from 35 to 70. That works fine - It unhides 25-70, and hides 71-116, but the subsets within don't stay hidden - I still have my YES for rule 2 and 3, but rows 25-30 & 40-50 aren't automatically hidden. I have to reselect the YES in the corresponding drop downs. Hopefully that makes sense. Is the functionality to have other rules automatically applied when another rule is changed easy to add? I can live with reselecting options if needed, but just wanted to ask. Trying to thread the needle between having a clever spreadsheet, without trying to be too clever about it all!

Thanks
 
Upvote 0
DavidWT87 said:
What I have noticed is that when I clear selections from my drop down boxes, it doesn't unhide the rows associated with that rule. Is it possible to do this?
Click to expand...

DavidWT87 said:
The other thing I have noticed is that changing a drop down doesn't always take everything with it. For example, I select 35, and it hides rows 25-70, and then select YES to my other 2 rules, and it removes rows 71-75 and 86-96 as I want it to.
Click to expand...


Try this:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("F14, F15, F16")) Is Nothing Then
    Range("25:70").EntireRow.Hidden = Range("F14") = "35"
    Range("71:116").EntireRow.Hidden = Range("F14") = "70"
    Range("25:30, 71:75").EntireRow.Hidden = Range("F15") = "YES"
    Range("40:50, 86:96").EntireRow.Hidden = Range("F16") = "YES"
  End If
End Sub
 
Upvote 0
DanteAmor said:
Try this:
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("F14, F15, F16")) Is Nothing Then
    Range("25:70").EntireRow.Hidden = Range("F14") = "35"
    Range("71:116").EntireRow.Hidden = Range("F14") = "70"
    Range("25:30, 71:75").EntireRow.Hidden = Range("F15") = "YES"
    Range("40:50, 86:96").EntireRow.Hidden = Range("F16") = "YES"
  End If
End Sub
Click to expand...

That seems to be throwing things off more. Have attached an image with a load of scrap stuff in to show you. F14 is 35, which I want to make hide rows 25-70. But it looks like the rules for F15 and F16 are interfering and the rows associated with those rules are staying visable, so 25-30 is still there, as is 40-50, but all other rows within the 25-70 from rule 1 have disappeared.

My guess is that because there are common rows associated with my F14 rule and the F15/F16 rules. If I just mess about with the F15/F16 rules, or add additional rules that deal with other rows not already having rules assigned to them, it does everything I want it to.

Just in case it was me not explaining well what I was trying to do, I'll give it another go.

The data in 25-70 is repeated in rows 71-116 and is a set of pressure tests, which can be done at either 35 or 70 MPa. The type of system determines whether testing is done at 35MPa, 70Mpa, or at both. So the idea here is if they do 35MPa testing, they select 35, and it hides the 2nd group of tests from rows 71-116, and if they select 70, it hides the other set of data. But they can also just leave it blank and both sets are kept.

I will then have a few more possible selections that will remove certain tests that wouldn't be needed from within the 25-70/71-116 groups. So if F14 is blank (and nothing is removed), and then YES is selected in both F15 and F16, the subsets associated with that rule will be removed from the 35 and 70 sections.

But if 35 is selected, then 25-70 is already removed, and saying YES to F15 will just remove 71-75 that haven't already been hidden by rule 1.

Similarly is 70 is selected, then 71-116 is already removed, and saying YES in F16, is only going to remove 40-50 because 86-96 is already hidden as a result of selecting 70 in the first place.

Sorry if this is all very confusing. You have been very helpful, and I think I can work with your initial answer, and just add in a couple of manual instructions if I need to.
 

Attachments

  • Excel Code Example.JPG
    Excel Code Example.JPG
    223.4 KB · Views: 7
Upvote 0
DavidWT87 said:
Sorry if this is all very confusing.
Click to expand...
That's right, it's very confusing.

You must explain each scenario. Which rows to hide and which rows to show?
1. empty, empty, empty
2. 35, empty, empty
3. 35, yes, empty
4. 35, empty, yes
5. 35, yes, yes
6. empty, yes, empty
7. empty, empty, yes
8. empty, yes, yes
9. 70, empty, empty
10. 70, yes, empty
11. 70, empty, yes
12. 70, yes, yes

What if you type 35, but there are one or two yes? I must take into account not only what you capture, but also what already exists in the cells.
So if you explain the above scenarios maybe I can find the complete solution.
 
Upvote 0
DanteAmor said:
That's right, it's very confusing.

You must explain each scenario. Which rows to hide and which rows to show?
1. empty, empty, empty
2. 35, empty, empty
3. 35, yes, empty
4. 35, empty, yes
5. 35, yes, yes
6. empty, yes, empty
7. empty, empty, yes
8. empty, yes, yes
9. 70, empty, empty
10. 70, yes, empty
11. 70, empty, yes
12. 70, yes, yes

What if you type 35, but there are one or two yes? I must take into account not only what you capture, but also what already exists in the cells.
So if you explain the above scenarios maybe I can find the complete solution.
Click to expand...
I always suspected I was wanting to be too clever, but I continue to appreciate your help and patience with me.

So those 12 combinations are all the potential combos I have, and this is what I am wanting each combo to show:

1. All rows are shown - no rules yet applied
2. 35 chosen hides rows 71-116
3. 35 chosen hides rows 71-116, F15 Yes then also hides rows 25-30
4. 35 chosen hides rows 71-116, F16 Yes then also hides rows 40-50
5. 35 chosen hides rows 71-116, F15 Yes then also hides rows 25-30, F16 Yes then hides rows 40-50 too.
6. No Rule 1 choice so both the groups associated with 35 & 70 are showing, so F15 Yes will hide rows 25-30 and rows 71-75
7. No Rule 1 choice so both the groups associated with 35 & 70 are showing, so F16 Yes will hide rows 40-50 and rows 86-96
8. No Rule 1 choice so both the groups associated with 35 & 70 are showing, so F15 Yes will hide rows 25-30 and rows 71-75 whilst F16 Yes will hide rows 40-50 and rows 86-96.
9. 70 chosen hides rows 25-70
10. 70 chosen hides rows 25-70, F15 Yes then also hides rows 71-75
11. 70 chosen hides rows 25-70, F16 Yes then also hides rows 86-96
12. 70 chosen hides rows 25-70, F15 Yes then also hides rows 71-75, F16 Yes then hides rows 86-96 too.
 
Upvote 0
Let's do it step by step.

Try the following, it contains all scenarios.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("F14, F15, F16")) Is Nothing Then
    Range("25:116").EntireRow.Hidden = False
    If Range("F14").Value = 35 Then
      '2. 35 chosen hides rows 71-116
      '3. 35 chosen hides rows 71-116, F15 Yes then also hides rows 25-30
      '4. 35 chosen hides rows 71-116, F16 Yes then also hides rows 40-50
      '5. 35 chosen hides rows 71-116, F15 Yes then also hides rows 25-30, F16 Yes then hides rows 40-50 too.
      Range("71:116").EntireRow.Hidden = True
      If Range("F15").Value = "YES" Then
        Range("25:30").EntireRow.Hidden = True
      End If
      If Range("F16").Value = "YES" Then
        Range("40:50").EntireRow.Hidden = True
      End If
    ElseIf Range("F14").Value = 70 Then
      '9. 70 chosen hides rows 25-70
      '10. 70 chosen hides rows 25-70, F15 Yes then also hides rows 71-75
      '11. 70 chosen hides rows 25-70, F16 Yes then also hides rows 86-96
      '12. 70 chosen hides rows 25-70, F15 Yes then also hides rows 71-75, F16 Yes then hides rows 86-96 too.
      Range("25:70").EntireRow.Hidden = True
      If Range("F15").Value = "YES" Then
        Range("71:75").EntireRow.Hidden = True
      End If
      If Range("F16").Value = "YES" Then
        Range("86:96").EntireRow.Hidden = True
      End If
    ElseIf Range("F14").Value = "" Then
      '6. No Rule 1 choice so both the groups associated with 35 & 70 are showing, so F15 Yes will hide rows 25-30 and rows 71-75
      '7. No Rule 1 choice so both the groups associated with 35 & 70 are showing, so F16 Yes will hide rows 40-50 and rows 86-96
      '8. No Rule 1 choice so both the groups associated with 35 & 70 are showing, so F15 Yes will hide rows 25-30 and rows 71-75 whilst F16 Yes will hide rows 40-50 and rows 86-96.
      If Range("F15").Value = "YES" Then
        Range("25:30, 71:75").EntireRow.Hidden = True
      End If
      If Range("F16").Value = "YES" Then
        Range("40:50, 86:96").EntireRow.Hidden = True
      End If
    End If
  End If
End Sub


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
 
Upvote 0
Solution
DanteAmor said:
Let's do it step by step.

Try the following, it contains all scenarios.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("F14, F15, F16")) Is Nothing Then
    Range("25:116").EntireRow.Hidden = False
    If Range("F14").Value = 35 Then
      '2. 35 chosen hides rows 71-116
      '3. 35 chosen hides rows 71-116, F15 Yes then also hides rows 25-30
      '4. 35 chosen hides rows 71-116, F16 Yes then also hides rows 40-50
      '5. 35 chosen hides rows 71-116, F15 Yes then also hides rows 25-30, F16 Yes then hides rows 40-50 too.
      Range("71:116").EntireRow.Hidden = True
      If Range("F15").Value = "YES" Then
        Range("25:30").EntireRow.Hidden = True
      End If
      If Range("F16").Value = "YES" Then
        Range("40:50").EntireRow.Hidden = True
      End If
    ElseIf Range("F14").Value = 70 Then
      '9. 70 chosen hides rows 25-70
      '10. 70 chosen hides rows 25-70, F15 Yes then also hides rows 71-75
      '11. 70 chosen hides rows 25-70, F16 Yes then also hides rows 86-96
      '12. 70 chosen hides rows 25-70, F15 Yes then also hides rows 71-75, F16 Yes then hides rows 86-96 too.
      Range("25:70").EntireRow.Hidden = True
      If Range("F15").Value = "YES" Then
        Range("71:75").EntireRow.Hidden = True
      End If
      If Range("F16").Value = "YES" Then
        Range("86:96").EntireRow.Hidden = True
      End If
    ElseIf Range("F14").Value = "" Then
      '6. No Rule 1 choice so both the groups associated with 35 & 70 are showing, so F15 Yes will hide rows 25-30 and rows 71-75
      '7. No Rule 1 choice so both the groups associated with 35 & 70 are showing, so F16 Yes will hide rows 40-50 and rows 86-96
      '8. No Rule 1 choice so both the groups associated with 35 & 70 are showing, so F15 Yes will hide rows 25-30 and rows 71-75 whilst F16 Yes will hide rows 40-50 and rows 86-96.
      If Range("F15").Value = "YES" Then
        Range("25:30, 71:75").EntireRow.Hidden = True
      End If
      If Range("F16").Value = "YES" Then
        Range("40:50, 86:96").EntireRow.Hidden = True
      End If
    End If
  End If
End Sub


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
Click to expand...
Quickly popped that in before I left work and I think it was doing what I was hoping.

Thank you so much for your help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Creating a 'Reset' button for a text form, plus additional buttons
Replies
2
Views
309
WEB78
W
R
  • Solved
Hide rows according to checkboxes on seperate sheet
Replies
8
Views
632
John_w
J
N
  • Question Question
Hiding/showing rows between two values
Replies
1
Views
480
Joe4
Joe4
C
  • Question Question
Copy to Clipboard but hide rows that do not have data in certain cells
Replies
0
Views
55
curtoliver68
C
J
  • Question Question
Hide header row if range of cells in column is 0
2
Replies
17
Views
882
jamesyvoi
J

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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