How to automatically hide rows in sheet based on different sheet cell value

MakeMeProud · Jul 2nd 2020

Hello,

I'm new to VBA and have a problem, so I hope - you can help me

If I have two worksheets, first called DATA and second called FORM, and I want to hide rows in FORM based on the cell value (drop down list with 7 options, which should hide different ranges) in DATA. I wrote the code in FORM sheet and I made in cell B1 easy function like B1=DATA!A1 - and after changing this value in DATA the value changes, but nothing is automatically hidden. I can hide if after I enter the cell B1, press the formula on formula bar and click enter - then it works.

My code is written in FORM sheet and looks like this:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate

If Not Application.Intersect(Range("B1"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
        
    Case Is = "Not Applicable": Rows("12:18").EntireRow.Hidden = True
        Range("A1:A11").EntireRow.Hidden = False
    
    Case Is = "1": Rows("14:18").EntireRow.Hidden = True
        Range("A1:A13").EntireRow.Hidden = False

    Case Is = "2": Rows("15:18").EntireRow.Hidden = True
        Range("A1:A14").EntireRow.Hidden = False
        
    Case Is = "3": Rows("16:18").EntireRow.Hidden = True
        Range("A1:A15").EntireRow.Hidden = False
        
    Case Is = "4": Rows("17:18").EntireRow.Hidden = True
        Range("A1:A16").EntireRow.Hidden = False

    Case Is = "5": Rows("18:18").EntireRow.Hidden = True
        Range("A1:A17").EntireRow.Hidden = False

    Case Is = "6": Rows("18").EntireRow.Hidden = True
        Range("A1:A18").EntireRow.Hidden = False

 End Select
End If
End Sub

Display More

Could you tell me how to change it to make it automatic? I mean when B1 change value, my rows are hidden or unhidden.

Fluff13 · Jul 2nd 2020

Hi & welcome to the board.

How about

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B1"), Range(Target.Address)) Is Nothing Then
 With Sheets("Form")
   Select Case Target.Value
   
   Case Is = "Not Applicable": .Rows("12:18").EntireRow.Hidden = True
   .Range("A1:A11").EntireRow.Hidden = False
   
   Case Is = "1": .Rows("14:18").EntireRow.Hidden = True
   .Range("A1:A13").EntireRow.Hidden = False
   Case Is = "2": .Rows("15:18").EntireRow.Hidden = True
   .Range("A1:A14").EntireRow.Hidden = False
   
   Case Is = "3": .Rows("16:18").EntireRow.Hidden = True
   .Range("A1:A15").EntireRow.Hidden = False
   
   Case Is = "4": .Rows("17:18").EntireRow.Hidden = True
   .Range("A1:A16").EntireRow.Hidden = False
   Case Is = "5": .Rows("18:18").EntireRow.Hidden = True
   .Range("A1:A17").EntireRow.Hidden = False
   Case Is = "6"
   .Range("A1:A18").EntireRow.Hidden = False
   End Select
 End With
End If
End Sub

Display More

This needs to go in the Data sheet module

How to automatically hide rows in sheet based on different sheet cell value

Participate now!

Share

Tags