No announcement yet.

Hide Rows based on Drop-down List

  • Filter
  • Time
  • Show
Clear All
new posts

  • Hide Rows based on Drop-down List

    Let me preface this by saying I'm not new to Excel, but am fairly new to using Macros and VBA. I spent all day searching through the forums and couldn't quite find anything that fit what I'm looking to do.

    I have a workbook with multiple worksheets. On Sheet1 I have a drop down menu with options for agent type in cell F1: "Agent A" and "Agent B", etc, which users will select as applicable. On Sheet2 I have specific rows of scores that I need to either be shown or hidden depending on the selection.

    So if user selects Agent A, I need Sheet2, Rows 11-16 to show but if user selects Agent B, I need Rows 11-16 to be hidden.

    The drop down menu is based on data validation (perhaps not the 'right' way to do such a short list, but it's been a few years so my Excel skills are pretty rust at the moment) and that data is located on Sheet3 in cells A76:A77

    I've tried a few versions of code that I've found here, but none of it has worked quite right - either because it's not what I need or I'm just not quite sure how to edit it properly.

    Any help you can provide will be greatly appreciated. I will be at work, so if I don't respond promptly to questions I'm not being rude, just busy.

    Last edited by lexibelle5110; December 10th, 2010, 00:31. Reason: Added cell references

  • #2

    Re: Hide Rows based on Drop-down List

    Right click the tab of sheet1 and copy and paste in this code. This assumes the drop down is in cell A1 on sheet1. Change as required.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("A1")) Is Nothing Then
            With Sheet2
                .Rows("11:16").EntireRow.Hidden = Target.Value = "Agent A"
            End With
        End If
    End Sub
    Note: Sheet references are sheet code names - not worksheet (tab) names. Adjust sheet code names as needed.

    Forum Rules | Message to Cross Posters | How to use Tags