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.

 

[alansidman]

Place this in a worksheet module

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]

How about (code to a sheet module)

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]

Correct version of my 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]

Place this in a worksheet module

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]

Correct version of my 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]

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]

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

 

[Hamza Oza]

Correct version of my 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]

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

Hope it helps.