Dynamicly Hide/Unhide rows based on cell value

[Awesome Wally]

I am looking for code to hide/unhide Rows based on a cell value. Thanks in advance for your help!

For Example:
Range A1:A100
Reference Cell Value (B1) = 10
**This value needs to be dynamic

First Click Unhide A1-A10 (10 Rows)
Second Click Hide Whole Range
Attached below is the current code I am using

Sub HideUnhide()
    Range("1:100").EntireRow.Hidden = Not Range("1:100").EntireRow.Hidden
End Sub

 

[YasserKhalil]

May be something like that (Put the code in worksheet change) ..
I have inserted a row so as to make B1 visible all the time and your data would start at row 2

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        Cells.EntireRow.Hidden = False
        On Error GoTo Skipper
        Rows(2).Resize(Target.Value).Hidden = True
Skipper:
        Target.Activate
    End If
End Sub

 

[Awesome Wally]

Yasser, Thanks for the quick response. The code you supplied hides the first 10 rows. I would like to have those be visible and hide the rest. Additionally, I want to make sure the rows beyond 100 remain visible at all times.
Thanks again for looking!

 

[YasserKhalil]

Do you mean that

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        Cells.EntireRow.Hidden = False
        Rows("2:100").Hidden = True
        On Error GoTo Skipper
        Rows(2).Resize(Target.Value).Hidden = False
       
Skipper:
        Target.Activate
    End If
End Sub

 

[Awesome Wally]

Yasser, That is perfect!
How would I amend to assign to a shape instead of worksheet change event? I know how to assign I just don't know how to move from Change event to module.

 

[YasserKhalil]

You're welcome
Try that

Sub Test()
        Cells.EntireRow.Hidden = False
        Rows("2:100").Hidden = True
        On Error GoTo Skipper
        Rows(2).Resize(Range("B1").Value).Hidden = False
Skipper:
End Sub

 

[Awesome Wally]

That works great to unhide the desired rows but doesn't hide All rows on second click.

First Click Unhide desired rows (value in B1)
Second Click Hide all rows except Row 1

Thanks for staying on this with me. I have a lot of VBA to learn still!

 

[YasserKhalil]

Try this version

Sub Test()
    If b = False Then
        Cells.EntireRow.Hidden = False
        Rows("2:101").Hidden = True
        On Error GoTo Skipper
        Rows(2).Resize(Range("B1").Value).Hidden = False
Skipper:
        b = True
    Else
        Cells.EntireRow.Hidden = True
        Rows(1).EntireRow.Hidden = False
        Application.Goto Range("A1"), True
        b = False
    End If
End Sub

 

[Awesome Wally]

Similar story. Looks like the issue is on hiding the whole range still.

 

[YasserKhalil]

I didn't understand the problem well. What's the problem now?
Have you checked the code well?

 

[Awesome Wally]

I have uploaded a file to help explain what I'm attempting to do.
1) Click run to Hide all rows except number of rows in B1
2) Click run again to hide all rows

Similar to the code below but with the ability to set the number of visable rows dynamically using B1

Sub HideUnhide()
    Range("2:101").EntireRow.Hidden = Not Range("2:101").EntireRow.Hidden
End Sub

 

[SirJB7]

Hi, Awesome Wally!

Give a look at the attached file. Yes, you guessed it, press the light blue button.
This is the code:

Option Explicit

Sub DrJekill_MrHyde()
    ' constants
    Const ksWS = "Hoja1"
    Const ksRows = "B1"
    ' declarations
    Dim rng As Range, iRow As Integer, iStatus As Integer
    ' start
    With Worksheets(ksWS)
        Set rng = .Range(Rows(2), Rows(100))
        iRow = .Range(ksRows).Value
    End With
    With rng
        If .Cells(1, 1).EntireRow.Hidden Then
            iStatus = 1
        Else
            iStatus = 2
        End If
    End With
    ' process
    Select Case iStatus
        Case 1
            If iRow > 0 Then
                Range(rng.Rows(1), rng.Rows(iRow)).EntireRow.Hidden = False
            Else
                MsgBox "R U kidding?", vbOKOnly + vbQuestion, "What's up?"
            End If
        Case 2
            rng.EntireRow.Hidden = True
    End Select
    ' end
    Set rng = Nothing
    Beep
End Sub

Regards!

 

[Awesome Wally]

SirJB7 you did it again! Thanks for the great work, this is perfect!

 

[SirJB7]

Hi, Awesome Wally!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Just take care of this: cell B1 has data validation set between 0 and 99, and this is related to the range 2:100 in the code (0 just for displaying an error). You should update it to named ranges so as to make it more flexible.

Regards!

 

[YasserKhalil]

Here's a sample of modified code

 

[bobbycheever]

Here's a sample of modified code

I used this code successfully "at first", but I am finding that it has become "buggy". It seemed to do well, but when I was editing other aspects (formatting/layout/data) it started acting up. It would not always hide/unhide. Now It only works the first time I use the button after opening the worksheet. I only changed the range of hidden cells to be "2:20" and the cell determining how many cells to hide/unhide to "J1". Any Ideas? I am new at VBA so It is probably something simple I am overlooking.