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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### alansidman

##### Well-known Member
Place this in a worksheet module
VBA Code:
``````Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("A1")
If Target = 1 Then
Rows("6:10").Hidden = True
ElseIf Target = 2 Then
Rows("6:10").Hidden = False
Rows("7:10").Hidden = True
ElseIf Target = 3 Then
Rows("6:10").Hidden = False
Rows("8:10").Hidden = True
End If
End Sub``````

#### LazyBug

##### Board Regular
How about (code to a sheet module)
VBA Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim rcn&
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Cells(1, 1)) Is Nothing Then
rcn = Target + 5
Rows("5:10").Hidden = False
Rows(rcn & ":10").Hidden = True
End If
End Sub``````

#### LazyBug

##### Board Regular
Correct version of my code:
VBA Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim rcn&
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Cells(1, 1)) Is Nothing Then
rcn = Target + 5
Rows("5:10").Hidden = False
If rcn <= 10 Then Rows(rcn & ":10").Hidden = True
End If
End Sub``````

#### Hamza Oza

##### New Member

Place this in a worksheet module
VBA Code:
``````Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("A1")
If Target = 1 Then
Rows("6:10").Hidden = True
ElseIf Target = 2 Then
Rows("6:10").Hidden = False
Rows("7:10").Hidden = True
ElseIf Target = 3 Then
Rows("6:10").Hidden = False
Rows("8:10").Hidden = True
End If
End Sub``````
Its giving me a subscript out of range error message. Not sure what this means, I am very new to VBA.

#### Hamza Oza

##### New Member
Correct version of my code:
VBA Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim rcn&
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Cells(1, 1)) Is Nothing Then
rcn = Target + 5
Rows("5:10").Hidden = False
If rcn <= 10 Then Rows(rcn & ":10").Hidden = True
End If
End Sub``````
Its giving me a subscript out of range error. Not sure what this means. I am completely new to VBA.

#### LazyBug

##### Board Regular

1) With your workbook active press Alt+F11 for VBA editor.
2) Double-clicking the sheet object with his name in left corner opens its code module where we can add event procedures (macros).
3) Copy and Paste the code I posted in #4 into the main right hand pane that opens at step 2.
4) Press Ctrl+S to save changes in the code of your workbook.
5) Press Alt+F11 again to activate the main Excel window with table.

Try to change cell A1 value.

#### alansidman

##### Well-known Member
When you get the error message, click on Debug and then tell us which line of code is highlighted.

#### Hamza Oza

##### New Member
Correct version of my code:
VBA Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim rcn&
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Cells(1, 1)) Is Nothing Then
rcn = Target + 5
Rows("5:10").Hidden = False
If rcn <= 10 Then Rows(rcn & ":10").Hidden = True
End If
End Sub``````
This has worked well for me. However, would it not automatically update based on the cell value in A1 if A1 is formula driven? So if A1 is driven from another cell and the cell value of that other cell changes from say 1 to 2, the rows do not hide/unhide automatically.
Is there a way to get it to update automatically?

#### LazyBug

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

Hope it helps.

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

1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

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