# Worksheet change to hide rows based on cell value

#### Mark_G

##### Board Regular
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

### 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
Is F37 being changed manually, or are the numbers a formula result?

#### Mark_G

##### Board Regular
it is being manually changed. There is data valadation that only allows 0-10 to be entered.

Mark

#### Von Pookie

##### MrExcel MVP
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

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
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
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 ?

Replies
15
Views
561
Replies
14
Views
186
Replies
11
Views
114
Replies
15
Views
869
Replies
0
Views
55