Hide/unhide different rows based on values in multiple cells

[foleycormac]

Hi All.

I really haven't a clue about VBA but I managed to figure out how to hide/unhide a group of rows based on the value of a particular cell.
However, I need to hide/unhide multiple groups of cells based on a yes/no value in other cells.

For example, if C12 is "No" then rows 13:18 are hidden.
If C20 is "No" then rows 21:36 are hidden.

There are 12 sets of rows I need to hide if the associated cell is "No"

 

[foleycormac]

OK, assuming that cell H2 allows numeric entries only from 1 to 9 (which you can control with Data Validation), put this code on your Details sheet:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rws As String
    Dim cls As String

'   Only run if one cell updated
    If Target.CountLarge > 1 Then Exit Sub

'   See what cell was update
    Select Case Target.Address

'       Check cell H2
        Case "$H$2"
'           First unhide all columns
            Sheets("Pricing").Columns("K:S").EntireColumn.Hidden = False
'           Get first column to hide, depending on value in H2
            cls = Chr(74 + Target.Value)
'           Hide columns on pricing sheet
            Sheets("Pricing").Columns(cls & ":S").EntireColumn.Hidden = True
           
'       Check to see if B21 was updated
        Case "$B$21"
            rws = "8:15"
'       Check to see if B22 was updated
        Case "$B$22"
            rws = "16:33"
'       Check to see if B23 was updated
        Case "$B$23"
            rws = "34:48"
'       continue on for cells B24-B32
'       ...
'           ...
'       Check to see if B23 was updated
        Case "$B$33"
            rws = "161:166"
    End Select
   
'   Check to see if rws updated
    If rws <> "" Then
        Select Case Target.Value
            Case "Yes"
                Sheets("Pricing").Rows(rws).EntireRow.Hidden = False
            Case "No"
                Sheets("Pricing").Rows(rws).EntireRow.Hidden = True
        End Select
    End If
                   
End Sub

Note that since there is no distinct pattern in how many rows to hide, you will need a separate Case clause for each cell in B21:B33. I did B21-B23 and B33 for you. You can fill in the rest. Just follow the same method I did for the first three. The spots with the "..." is where you will want to insert that.

Thanks a million for the quick turnaround with the help.

I must be doing something wrong though.
It is hiding the columns perfectly but it's not making any difference to the rows.

Here's the code I entered:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rws As String
Dim cls As String

' Only run if one cell updated
If Target.CountLarge > 1 Then Exit Sub

' See what cell was update
Select Case Target.Address

' Check cell H2
Case "$H$2"
' First unhide all columns
Sheets("Pricing").Columns("K:S").EntireColumn.Hidden = False
' Get first column to hide, depending on value in H2
cls = Chr(74 + Target.Value)
' Hide columns on pricing sheet
Sheets("Pricing").Columns(cls & ":S").EntireColumn.Hidden = True

' Check to see if B21 was updated
Case "$B$21"
rws = "8:15"
' Check to see if B22 was updated
Case "$B$22"
rws = "16:33"
' Check to see if B23 was updated
Case "$B$23"
rws = "34:48"
' Check to see if B24 was updated
Case "$B$24"
rws = "49:61"
' Check to see if B25 was updated
Case "$B$25"
rws = "62:75"
' Check to see if B26 was updated
Case "$B$26"
rws = "76:98"
' Check to see if B27 was updated
Case "$B$27"
rws = "99:120"
' Check to see if B28 was updated
Case "$B$28"
rws = "121:126"
' Check to see if B29 was updated
Case "$B$29"
rws = "127:139"
' Check to see if B30 was updated
Case "$B$30"
rws = "140:147"
' Check to see if B31 was updated
Case "$B$31"
rws = "148:154"
' Check to see if B32 was updated
Case "$B$32"
rws = "155:160"
' Check to see if B33 was updated
Case "$B$33"
rws = "161:166"
End Select

' Check to see if rws updated
If rws <> "" Then
Select Case Target.Value
Case "Yes"
Sheets("Pricing").Rows(rws).EntireRow.Hidden = False
Case "No"
Sheets("Pricing").Rows(rws).EntireRow.Hidden = True
End Select
End If

End Sub




I noticed that the last Case clause that you had written had B23 rather than B33 so I changed it.
I also tried it as written but that didn't make any difference either.
Would it make a difference if I had the Yes/No in B21:B33 selected by data validation or not?
At the moment it is data validation

 

[Joe4]

Would it make a difference if I had the Yes/No in B21:B33 selected by data validation or not?

No, but it does make a difference if it "YES" or "yes" instead of "Yes".
Or "NO" or "no" instead of "No".
And make sure that there are not any extra spaces in there.
And note that it is hiding the rows on the "Pricing" sheet based on the selections in the "Details" sheet.

Do you have any other VBA code which may be undoing what this code is doing?

If you cannot figure it out, I would recommend playing a "break point" in the beginning your code, so that it stops there and you can see what it is doing by then pressing F8 repeatedly to go through the code line-by-line. Then you will be able to see exactly what it is doing.

 

[foleycormac]

No, but it does make a difference if it "YES" or "yes" instead of "Yes".
Or "NO" or "no" instead of "No".
And make sure that there are not any extra spaces in there.
And note that it is hiding the rows on the "Pricing" sheet based on the selections in the "Details" sheet.

Do you have any other VBA code which may be undoing what this code is doing?

If you cannot figure it out, I would recommend playing a "break point" in the beginning your code, so that it stops there and you can see what it is doing by then pressing F8 repeatedly to go through the code line-by-line. Then you will be able to see exactly what it is doing.

It's working great now thanks.
I closed down a few other workbooks I had open including this one and it's working perfectly.

Thanks so much.

What's the best way to start understanding how VBA works as it's something I'd like to learn.

 

[foleycormac]

It's working great now thanks.
I closed down a few other workbooks I had open including this one and it's working perfectly.

Thanks so much.

What's the best way to start understanding how VBA works as it's something I'd like to learn.

If I wanted to ask another few questions about the same workbook but a different subject, should I ask it in this thread or start a new thread?

 

[Joe4]

What's the best way to start understanding how VBA works as it's something I'd like to learn.

There are numerous ways. Getting a good introductory book may be a good way to go about it, like this one here: Microsoft Excel 2019 VBA and Macros
There are also some good videos and tutorials you can find on-line with Google and YouTube searches.
Another good way is to read through a bunch of posts here on the forums.
And feel free to ask questions here, especially about answers you may have gotten, but don't fully comprehend.
(Feel free to ask about any part of my code!)

If I wanted to ask another few questions about the same workbook but a different subject, should I ask it in this thread or start a new thread?

If it is a different subject, definitely start a new thread. That way, if I do not know the answer, others will see the thread as a new, unanswered question, and will have a better chance of getting a reply. You typically only want to continue on in the same thread if you have a follow-up question on the original question.

 

[foleycormac]

Thanks again Joe, I'll look into that book.
This is a great website!

 

[Joe4]

Glad to have helped.

I noticed that the last Case clause that you had written had B23 rather than B33 so I changed it.

I just noticed this. Note that incorrect reference was just a part of the comment line, so it has no effect on the code.
Comments are just notes to yourself (or whoever is reading the code) to help explain what is going on.
I like to use them a lot. I think they are a good teaching tool, to kind of show you what is going on each step of the way.