# Hide/unhide variable number of rows

#### Hamza Oza

##### New Member
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

##### New Member
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.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### LazyBug

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

#### Hamza Oza

##### New Member
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

##### New Member
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

##### New Member
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

#### Attachments

• Screenshot 2021-01-01 at 16.41.53.png
85.1 KB · Views: 1

@cafeannalisa

Replies
8
Views
442
Replies
15
Views
2K
Replies
4
Views
150
Replies
13
Views
282
Replies
3
Views
52

1,126,996
Messages
5,622,078
Members
415,875
Latest member
Tarali

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back