asked on

hiding rows on one spreadsheet based on data entered in to a row on another worksheet

Excel 2010/2013

Hi and thank in advance,

I added two worksheets: Choice and Checklist. On the Choice worksheet I created a drop down list in cell B4. My logic is. If I select Low or Moderate, the following rows (4, 5, & 6) on worksheet: Checklist, will be hidden. If High is selected all rows are displayed,

I Used Alt F11, Insert,, Module and added the code below and no luck

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Checklist")
Select Case Target.Address(False, False)
Case "B4"
.Rows(4).Rows.Hidden = Target.Value = ""
.Rows(5).Rows.Hidden = Target.Value = ""
.Rows(6).Rows.Hidden = Target.Value = ""
' Case "B13"
' .Rows(15).Rows.Hidden = Target.Value = ""
' .Rows(35).Rows.Hidden = Target.Value = ""
' .Rows(38).Rows.Hidden = Target.Value = ""
End Select
End With
End Sub

Any help will be appreciated. Thanks!
Book1.xlsx

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cesemj

ASKER

I will try and let you know. Thanks

cesemj

ASKER

I Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert Menu and paste in the code. Press ALT + Q to close the code window and no luck. Can you tell what or which step I am doing wrong?

cesemj

ASKER

I got it, Thanks. should have just closed the VBA editor. Refreshed the worksheet and no issues. yabba dabbo do. Thanks.

Shums Faruk

Right Click on Sheet Name "Source", then click on View Code and paste above code, then click "X" window for Visual Basic.

cesemj

ASKER

Thanks.

Shums Faruk

You're Welcome! Glad I was able to help.

Roy Cox

The code can be written more concisely

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$4" Then
        Sheets("Checklist").Rows("4:6").EntireRow.Hidden = Range("B4") = "Moderate" Or Range("B4") = "Low"
    End If
End Sub

Open in new window