Hide and unhide rows based on drop down cell list selection (Microsoft 365)

[JaneShillam]

Hi Guys,

Please be kind I am a total newbie trying to learn some new skills

I have created a drop down list on my spreadsheet and want another section (rows 33:44) to remain hidden unless anything other than TBC selected

I have created the below but just cant get in to run, what am I missing ?

 

[Skybluekid]

Do you want it to hide the rows when the cell value changes, on a worksheet? If so, you may need to place this routine on the Actual Sheet and make it a Change Event.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this Worksheet_Change event code. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B11")) Is Nothing Then Rows("33:44").Hidden = Range("B11").Value = "TBC"
End Sub

 

[kvsrinivasamurthy]

Remove the text ByVal target As Range

 

[JaneShillam]

Thanks Peter,

Just tried, worked perfect and I can see what I was doing wrong for next time

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[JaneShillam]

Hi Guys,

So back again trying to replicate the above in a new worksheet but even after creating a new Module against sheet1 and adding the below when I click Play or Run the Macro is now selectable to Run !?

I know I am doing something really dumb, but after spending a lot of time trying to resolve myself and watching several videos on Youtube (99.9% of which all bang in about recording macros) I am still stuck ... : (

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    If Range("B92") = "Yes" Then
       
        Rows("101:120").EntireRow.Hidden = False
   
    Else
       
        Rows("101:120").EntireRow.Hidden = True
   
    End If
   
   
    If Range("B93") = "Yes" Then
       
        Rows("122:140").EntireRow.Hidden = False
   
    Else
       
        Rows("122:140").EntireRow.Hidden = True
   
    End If
   
   
    If Range("B94") = "Yes" Then
       
        Rows("141:158").EntireRow.Hidden = False
   
    Else
       
        Rows("141:158").EntireRow.Hidden = True
   
    End If
   
   
    If Range("B95") = "Yes" Then
       
        Rows("160:172").EntireRow.Hidden = False
   
    Else
       
        Rows("160:172").EntireRow.Hidden = True
   
    End If
   
    If Range("B96") = "Yes" Then
       
        Rows("174:179").EntireRow.Hidden = False
   
    Else
       
        Rows("174:179").EntireRow.Hidden = True
   
    End If
   
   
    If Range("B97") = "Yes" Then
       
        Rows("182:188").EntireRow.Hidden = False
   
    Else
       
        Rows("182:188").EntireRow.Hidden = True
   
    End If

End Sub

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

.. when I click Play or Run ..

You don't "run" worksheet event codes (eg Worksheet_SelectionChange) as such

Worksheet_SelectionChange code automatically runs any time you change your selection on the worksheet.

Don't you need Worksheet_Change code like last time, not Worksheet_SelectionChange?