Hide/unhide variable number of rows

Hamza Oza

New Member
Joined
Nov 16, 2020
Messages
12
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.
 

Some videos you may like

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
Joined
Feb 26, 2007
Messages
6,141
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Feb 28, 2020
Messages
156
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Feb 28, 2020
Messages
156
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Nov 16, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 16, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 28, 2020
Messages
156
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 26, 2007
Messages
6,141
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
When you get the error message, click on Debug and then tell us which line of code is highlighted.
 

Hamza Oza

New Member
Joined
Nov 16, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 28, 2020
Messages
156
Office Version
  1. 2010
Platform
  1. Windows
You can use one another cell for formula, e.g. D3.
Then just equate A1 with D3.

Hope it helps.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top