Worksheet change to hide rows based on cell value

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123
I am trying to work on a worksheet change event.

Here is what i got

Set rng = Range("F37")

Range("A21" & numRows":A30").Select
Selection.EntireRow.Hidden = True

What I want is that every time the value of cell f37 changes I want to hide the rows that are in the range.

Cell f37 will always have a number in it from 0-10

The range I want to hide is A21:A30.

If the vaule in F37 is 0, I want to hide A21:A30
If the value in F37 is 1, I want to hide A22:A30
If the value in F37 is 2, I want to hide A23:A30
and so on so that
If the value in F37 is 9, I want to hide A430:a30
If the value in F37 is 10, I won't want to hide any rows

Any suggestions on how to do it.

Mark
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Is F37 being changed manually, or are the numbers a formula result?
 

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123
it is being manually changed. There is data valadation that only allows 0-10 to be entered.

Mark
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
This is completely hard-coded, but works for me:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim HideRows As Range, ViewRows As Range

If Intersect([F37], Target) Is Nothing Then Exit Sub

Select Case [F37].Value
    Case Is = 0
        Set HideRows = Rows("21:30")
        Set ViewRows = Nothing
    Case Is = 1
        Set HideRows = Rows("22:30")
        Set ViewRows = Rows("21:21")
    Case Is = 2
        Set HideRows = Rows("23:30")
        Set ViewRows = Rows("21:22")
    Case Is = 3
        Set HideRows = Rows("24:30")
        Set ViewRows = Rows("21:23")
    Case Is = 4
        Set HideRows = Rows("25:30")
        Set ViewRows = Rows("21:24")
    Case Is = 5
        Set HideRows = Rows("26:30")
        Set ViewRows = Rows("21:25")
    Case Is = 6
        Set HideRows = Rows("27:30")
        Set ViewRows = Rows("21:26")
    Case Is = 7
        Set HideRows = Rows("28:30")
        Set ViewRows = Rows("21:27")
    Case Is = 8
        Set HideRows = Rows("29:30")
        Set ViewRows = Rows("21:28")
    Case Is = 9
        Set HideRows = Rows("30:30")
        Set ViewRows = Rows("21:29")
    Case Is = 10
        Set HideRows = Nothing
        Set ViewRows = Rows("21:30")
End Select

On Error Resume Next
HideRows.Hidden = True
ViewRows.Hidden = False

End Sub
 

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123

ADVERTISEMENT

Thanks for the reply, it does just what I wanted it to do.

Now I would like to know how to have two different worksheet change evens on the same sheet. Along with the code from above, I also have some more code that copies certain cells, and pastes them. Both codes work if they are kept seperate, but as they are for the same sheet they need to be togther, right?

Here are the codes, what do I have to do to make them both work?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim HideRows As Range, ViewRows As Range
Dim rng As Range

UnprotectSheets

If Intersect([F38], Target) Is Nothing Then Exit Sub

Select Case [F38].Value
    Case Is = 0
        Set HideRows = Rows("21:30")
        Set ViewRows = Nothing
    Case Is = 1
        Set HideRows = Rows("22:30")
        Set ViewRows = Rows("21:21")
    Case Is = 2
        Set HideRows = Rows("23:30")
        Set ViewRows = Rows("21:22")
    Case Is = 3
        Set HideRows = Rows("24:30")
        Set ViewRows = Rows("21:23")
    Case Is = 4
        Set HideRows = Rows("25:30")
        Set ViewRows = Rows("21:24")
    Case Is = 5
        Set HideRows = Rows("26:30")
        Set ViewRows = Rows("21:25")
    Case Is = 6
        Set HideRows = Rows("27:30")
        Set ViewRows = Rows("21:26")
    Case Is = 7
        Set HideRows = Rows("28:30")
        Set ViewRows = Rows("21:27")
    Case Is = 8
        Set HideRows = Rows("29:30")
        Set ViewRows = Rows("21:28")
    Case Is = 9
        Set HideRows = Rows("30:30")
        Set ViewRows = Rows("21:29")
    Case Is = 10
        Set HideRows = Nothing
        Set ViewRows = Rows("21:30")
End Select

On Error Resume Next
HideRows.Hidden = True
ViewRows.Hidden = False


Set rng = Range("b11:b20, i11:i20, a21:i30, c32")

If Not Intersect(Target, rng) Is Nothing Then
    Intersect(Target, rng).Offset(48, 0).Value = Intersect(Target, rng).Value
End If


End Sub

Thanks, Mark
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
If the code that copies cells is a Worksheet_Change code, then yes, you will have to get them to play together nicely in the one sub.

Assuming the code you posted above is all of the code you want to be in the Worksheet_Change macro, try this. Instead of exiting the macro if F38 was not the changed cell, it will skip the Select Case statements and continue after the IF statement:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim HideRows As Range, ViewRows As Range
Dim rng As Range

Application.ScreenUpdating = False

'if more than 1 cell was changed, do nothing
If Target.Count > 1 Then Exit Sub

'if F38 was the cell that was changed, hide/unhide rows
If Not Intersect([F38], Target) Is Nothing Then
    Select Case [F38].Value
        Case Is = 0
            Set HideRows = Rows("21:30")
            Set ViewRows = Nothing
        Case Is = 1
            Set HideRows = Rows("22:30")
            Set ViewRows = Rows("21:21")
        Case Is = 2
            Set HideRows = Rows("23:30")
            Set ViewRows = Rows("21:22")
        Case Is = 3
            Set HideRows = Rows("24:30")
            Set ViewRows = Rows("21:23")
        Case Is = 4
            Set HideRows = Rows("25:30")
            Set ViewRows = Rows("21:24")
        Case Is = 5
            Set HideRows = Rows("26:30")
            Set ViewRows = Rows("21:25")
        Case Is = 6
            Set HideRows = Rows("27:30")
            Set ViewRows = Rows("21:26")
        Case Is = 7
            Set HideRows = Rows("28:30")
            Set ViewRows = Rows("21:27")
        Case Is = 8
            Set HideRows = Rows("29:30")
            Set ViewRows = Rows("21:28")
        Case Is = 9
            Set HideRows = Rows("30:30")
            Set ViewRows = Rows("21:29")
        Case Is = 10
            Set HideRows = Nothing
            Set ViewRows = Rows("21:30")
    End Select
    On Error Resume Next
    HideRows.Hidden = True
    ViewRows.Hidden = False
End If

'if F38 was *not* the changed cell, code continues here

Application.EnableEvents = False

Set rng = Range("B11:B20, I11:I20, A21:I30, C32")

If Not Intersect(Target, rng) Is Nothing Then
    Target.Offset(48, 0).Value = Target.Value
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
 

silverfang

New Member
Joined
Mar 19, 2020
Messages
11
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
If the code that copies cells is a Worksheet_Change code, then yes, you will have to get them to play together nicely in the one sub.

Assuming the code you posted above is all of the code you want to be in the Worksheet_Change macro, try this. Instead of exiting the macro if F38 was not the changed cell, it will skip the Select Case statements and continue after the IF statement:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim HideRows As Range, ViewRows As Range
Dim rng As Range

Application.ScreenUpdating = False

'if more than 1 cell was changed, do nothing
If Target.Count > 1 Then Exit Sub

'if F38 was the cell that was changed, hide/unhide rows
If Not Intersect([F38], Target) Is Nothing Then
    Select Case [F38].Value
        Case Is = 0
            Set HideRows = Rows("21:30")
            Set ViewRows = Nothing
        Case Is = 1
            Set HideRows = Rows("22:30")
            Set ViewRows = Rows("21:21")
        Case Is = 2
            Set HideRows = Rows("23:30")
            Set ViewRows = Rows("21:22")
        Case Is = 3
            Set HideRows = Rows("24:30")
            Set ViewRows = Rows("21:23")
        Case Is = 4
            Set HideRows = Rows("25:30")
            Set ViewRows = Rows("21:24")
        Case Is = 5
            Set HideRows = Rows("26:30")
            Set ViewRows = Rows("21:25")
        Case Is = 6
            Set HideRows = Rows("27:30")
            Set ViewRows = Rows("21:26")
        Case Is = 7
            Set HideRows = Rows("28:30")
            Set ViewRows = Rows("21:27")
        Case Is = 8
            Set HideRows = Rows("29:30")
            Set ViewRows = Rows("21:28")
        Case Is = 9
            Set HideRows = Rows("30:30")
            Set ViewRows = Rows("21:29")
        Case Is = 10
            Set HideRows = Nothing
            Set ViewRows = Rows("21:30")
    End Select
    On Error Resume Next
    HideRows.Hidden = True
    ViewRows.Hidden = False
End If

'if F38 was *not* the changed cell, code continues here

Application.EnableEvents = False

Set rng = Range("B11:B20, I11:I20, A21:I30, C32")

If Not Intersect(Target, rng) Is Nothing Then
    Target.Offset(48, 0).Value = Target.Value
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


Hi.
Is it possible that we can have changes in another sheet of same workbook.
For example, if we have sheet 1 and select cases as : if ("A1") =1 , then rows from B to D in Sheet 2 are hidden ?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,388
Messages
5,571,842
Members
412,420
Latest member
grace_abar
Top