Hide/unhide variable number of rows

[Hamza Oza]

Hi All,

I am trying to use a code that allows me to hide/unhide x number of rows based on another cell value. I also need this to update automatically when the cell value changes.

So for example if cell A1 is =1, I want it to show row 5 and hide rows 6-10, when the cell value changes to 2, I want it to show row 5-6 and hide rows 7-10, when the cell value changes to 3, I want it to sow rows 5-7 and hide rows 8-10 … and so forth.

I am very new to VBA coding, thus don't have a VBA I am using for the above. Any suggestions will be highly appreciated.

 

[Hamza Oza]

You can use one another cell for formula, e.g. D3.
Then just equate A1 with D3.

Hope it helps.

Hi. So lets say I am using a countif formula, are you saying I should try putting the countif in D3 and then in A1 put "=D3"? Because when I tried putting the countif in A1 it didn't work.

 

[LazyBug]

Hello!
Exactly! Put a formula to other cell (D3 in my example) and then put in A1 "=D3". It should work.

 

[Hamza Oza]

Hello!
Exactly! Put a formula to other cell (D3 in my example) and then put in A1 "=D3". It should work.

Hi, I tried it and unfortunately it does not work. It only works when I manually input the number into A1. I've been struggling with the VBA to make it update live. Not sure if there is any other solution?

 

[Hamza Oza]

I think @alansidman's formula works. Have to manually input the hide/unhide 25 times because I actually have 25 line items of data but it updates when cell value changes.
Not sure if there is an easier way though

 

[cafeannalisa]

While this is 'old' i seem to have basically the same question as Hamza so will post here first just in case
Columns J1-N1 have headers 1 2 3 4 5
I want to hide columns based on S1, which takes values 1-5
If S1 = 1 Hide columns headed 2-5 i.e. K:N
If S1 = 2 Hide columns headed 3-5 i.e.L:N
If S1 = 3 Hide columns headed 4-5 i.e.M:N
If S1 = 4 Hide column headed 5 i.e. N
[If S1 = 5 No hiding]
I have tried a version of alansidman's code above without success. And apologies but don't understand LazyBug's (i.e how to adapt)

Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Range(“S1")
If Target = 1 Then
columns(“K:N”).Hidden = True
ElseIf Target = 2 Then
columns("K:N").Hidden = False
columns(“L:N").Hidden = True
ElseIf Target = 3 Then
columns("K:N").Hidden = False
columns(“M:N").Hidden = True
ElseIf Target = 4 Then
columns("K:N").Hidden = False
columns(“N”).Hidden = True
End If
End Sub

 

[Fluff]

@cafeannalisa
Please start a new thread for this question. Thanks