Announcement

Collapse
No announcement yet.

Hide Rows based on Drop-down List

Collapse
X
  • 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.

    Thanks!
    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.

    Code:
    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.
    AAE
    ----------------------------------------------------

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

    Comment

    Working...
    X