• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamically hide specific rows based on cell value, greater than/less than

JoelHedberg

New Member
Hello everybody, first time here. I've run into some problems with a Excel sheet that I'm doing during my summer internship. I have a sheet with about 50 rows and I would like to hide some specific rows (CI9:CI16, to CN9:CN16) based on the value of the cell CI5. What I mean is that if the cell value of CI5 is equal to 1 I only want to show row number 1. If the value of CI5 is equal to for example 5, I want to hide all the rows with values greater than 5 and show the ones with values less than 5. I'll make sure to leave an image below to clarify further what I mean. I've tried with multiple macros but since I have very limited experience with VBA it's quite difficult to customize the code to my needs. Greatful for any help and feedback that I receive.

Joel
 

Attachments

JoelHedberg

New Member
Yeah I've tried filtering but it doesn't work the way i want it to. I want it to automatically hide and unhide the rows based on the value. I've actually written a macro that hides the rows now but it also hides other rows that are not meant to be hidden. This is because the rows that I want to hide are "connected" to the other rows that gets hidden.

Here is the macro:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("CI5"), Range(Target.Address)) Is Nothing Then  
       Select Case Target.Value
       Case Is = "1": Rows("CI10:CI19").EntireRow.Hidden = True
                             Rows("CI9").EntireRow.Hidden = False    
       Case Is = "2": Rows("CI11:CI19").EntireRow.Hidden = False  
                             Rows("C9:10").EntireRow.Hidden = True 
       Case Is = "3": Rows("CI12:CI19").EntireRow.Hidden = True
                             Rows("CI911").EntireRow.Hidden = False    
       Case Is = "4": Rows("CI13:CI19").EntireRow.Hidden = False  
                             Rows("C9:12").EntireRow.Hidden = True   
       Case Is = "5": Rows("CI14:CI19").EntireRow.Hidden = True
                             Rows("CI9:13").EntireRow.Hidden = False    
       Case Is = "6": Rows("CI15:CI19").EntireRow.Hidden = False  
                             Rows("C9:14").EntireRow.Hidden = True   
       Case Is = "7": Rows("CI16:CI19").EntireRow.Hidden = True
                             Rows("CI9:15").EntireRow.Hidden = False    
       Case Is = "8": Rows("CI17:CI19").EntireRow.Hidden = False  
                             Rows("C9:16").EntireRow.Hidden = True   
       Case Is = "9": Rows("CI18:19").EntireRow.Hidden = False    
      
 
      End Select
  End If
  End Sub
 

vletm

Excel Ninja
JoelHedberg
Wasn't You thread as Dynamically hide specific rows based on cell value, greater than/less than ?
next Question: if You hide some rows, then how to unhide those hidden rows, if those are hidden?

ps. with Your png-picture it won't work!
ps2. Did You read that my previous reply's the last line?
 

Marc L

Excel Ninja
Hi !​
  • Rows("CI12:CI19") : not rows but range !

  • Rows("CI911") : not rows but cell (range) …
 

p45cal

Well-Known Member
Try one of several ways:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$CI$5" Then
  Rows("9:19").Hidden = False
  Select Case Target.Value
    Case 1: Rows("10:19").Hidden = True
    Case 2: Rows("11:19").Hidden = True
    Case 3: Rows("12:19").Hidden = True
    Case 4: Rows("13:19").Hidden = True
    Case 5: Rows("14:19").Hidden = True
    Case 6: Rows("15:19").Hidden = True
    Case 7: Rows("16:19").Hidden = True
    Case 8: Rows("17:19").Hidden = True
    Case 9: Rows("18:19").Hidden = True
  End Select
End If
End Sub
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$CI$5" Then
  Rows("9:19").Hidden = False
  Select Case Target.Value
    Case 1 To 9: Rows(Target.Value + 9 & ":19").Hidden = True
  End Select
End If
End Sub
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$CI$5" Then
  Rows("9:19").Hidden = False
  For Each cll In Range("CI9:CI17").Cells
    If cll.Value > Target.Value Then cll.EntireRow.Hidden = True
  Next cll
End If
End Sub
 
Top