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