Automaticallly hide specific rows based on single cell value

[JoelHedberg]

Hello everybody, I've run into some problems with a worksheet that I'm doing during my internship. The idea is that based on the value of the cell CU2 I wan to hide rows with values greater than the cell value. So if CU2=3 I want to hide the rows 4-9 and so on. The easiset thing is probably to write a macro that does this but I've never touched VBA so it's pretty difficult for me to come up with a macro. I'll make sure to leave a picture to farther clarify what I'm wanting to do. Grateful for any feedback that I receive.

Joel

 

[JoelHedberg]

 

[GirishDhruva]

Hello everybody, I've run into some problems with a worksheet that I'm doing during my internship. The idea is that based on the value of the cell CU2 I wan to hide rows with values greater than the cell value. So if CU2=3 I want to hide the rows 4-9 and so on. The easiset thing is probably to write a macro that does this but I've never touched VBA so it's pretty difficult for me to come up with a macro. I'll make sure to leave a picture to farther clarify what I'm wanting to do. Grateful for any feedback that I receive.

Joel

Hi ,
if CU2=3 then hide rows 4-9 is OK what does that so on means....

 

[JoelHedberg]

I just meant that I want to hide all the rows with values higher than the value in CU2. So if CU2=1 rows 2-9 should be hidden, if CU2=2 rows 3-9 should be hidden. So all the rows with value greater than CU2 shall be hidden. I also want to unhide the rows if the value of CU2 changes back to for example 1.

 

[JoelHedberg]

I just meant that I want to hide all the rows with values higher than the value in CU2. So if CU2=1 rows 2-9 should be hidden, if CU2=2 rows 3-9 should be hidden. So all the rows with value greater than CU2 shall be hidden. I also want to unhide the rows if the value of CU2 changes back to for example 1.

 

[GirishDhruva]

All the rows means is your sheet has only 9 rows

If yes, below code will work

Sub JoelHedberg()
        If Range("CU2").Value = 1 Then
            Rows("2:9").EntireRow.Hidden = False
        ElseIf Range("CU2").Value = 2 Then
            Rows("3:9").EntireRow.Hidden = True
        ElseIf Range("CU2").Value = 3 Then
            Rows("4:9").EntireRow.Hidden = True
        ElseIf Range("CU2").Value = 4 Then
            Rows("5:9").EntireRow.Hidden = True
        ElseIf Range("CU2").Value = 5 Then
            Rows("6:9").EntireRow.Hidden = True
        ElseIf Range("CU2").Value = 6 Then
            Rows("7:9").EntireRow.Hidden = True
        ElseIf Range("CU2").Value = 7 Then
            Rows("8:9").EntireRow.Hidden = True
        End If
End Sub

 

[JoelHedberg]

Hi Girish, thank you very much for the rapid response. The picture that I linked is actually just a small part of the whole document, but I only want the part in the picture to be hidden/visible. The clarify: I have about 40 rows in total in the sheet but the rows that I want to be affected by the macro are CS8:CS18 stopping at CY8:CY18.