Hide/unhide variable number of rows

Hamza Oza

New Member
Joined
Nov 16, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
When you get the error message, click on Debug and then tell us which line of code is highlighted.
 
Upvote 0
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?
 
Upvote 0
You can use one another cell for formula, e.g. D3.
Then just equate A1 with D3.

Hope it helps.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top