CODEWITHSUNDEEP
vbaexcelshow-hide
Jewls567
Jewls567

Reputation: 13

Show/Hide Rows Per Dropdown Selection

I found code online as an example that I have tweaked to show or hide specific rows depending on the selection I choose within a dropdown in my Excel file.

The macro is not working no matter what I try.

My code is as follows (also attached screenshot of rows under question 2 (2a - 2d) that are not showing/hiding) Screenshot of Excel Doc

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$F$13" Then
If Range("F13").Value = "Yes" Then
Rows("14:17").EntireRow.Hidden = False
End If

If Range("F13").Value = "No" Then
Rows("14:17").EntireRow.Hidden = True
End If

If Range("F13").Value = " " Then
Rows("14:17").EntireRow.Hidden = True
End If
End Sub

Upvotes: 0

Views: 3392

Answers (2)

Rik Sportel
Rik Sportel

Reputation: 2679

There is an End If missing. I assume the value of the target cell (F13) needs to be tested for it's value. If the value is "Yes", it should unhide row 14:17, if it is " " (spacebar) it should hide them and if it is "No" is should hide them as well. Other values will not affect the hiding/unhiding of the rows. There should be a second End If before End Sub, so that all the if-statements above are wrapped within the Address check.

Also note that this code should be placed in the worksheet itself, since you want to hook into the Worksheet_Change event.

Try this in a worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$13" Then 'Check if the changed value is indeed in F13

    If Target.Value = "Yes" Then
        ActiveSheet.Rows("14:17").EntireRow.Hidden = False 'Show the rows if the value is Yes
    ElseIf Target.Value = "No" Then
        ActiveSheet.Rows("14:17").EntireRow.Hidden = True 'Hide them when it's No
    ElseIf Target.Value = " " Then
        ActiveSheet.Rows("14:17").EntireRow.Hidden = True 'Or space
    End If

End If
End Sub

Other remarks:

  • Instead of ActiveSheet you can also use Me (Me.Rows...) In this scenario they probably do the same. However, if you change the value on a worksheet from another worksheet (e.g. formula that recalculates), Me will reference the changed worksheet that fires the event, whereas activeworksheet will affect the currently active sheet.
  • Use Target instead of referencing the Range again. Target is a range object that is already in memory. Hence execution will be faster compared to accessing the worksheet again.

Upvotes: 0

sous2817
sous2817

Reputation: 3960

This is a good example of properly intending your code helping you identify an issue. You're missing an End IF statement. Try this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$F$13" Then
    If Range("F13").Value = "Yes" Then
        Rows("14:17").EntireRow.Hidden = False
    End If

    If Range("F13").Value = "No" Then
      Rows("14:17").EntireRow.Hidden = True
    End If

    If Range("F13").Value = " " Then
        Rows("14:17").EntireRow.Hidden = True
    End If
End If
End Sub

You may also want to use:

If Range("F13").Value = ""

instead of 

If Range("F13").Value = " "

Upvotes: 1

Related Questions