Hiding / unhiding rows based on change of a cell

[jack]

I can't seem to get the following code to work. I want to hide / unhide a
series of rows on sheet1 based on the value in a cell in sheet2. Once I
understand how to get it to work, I want to expand it to approx. 40 cells on
sheet2 to hide / unhide groups of rows on sheet1.
Do I put the code in sheet1, sheet2 or the workbook?
Any ideas to steer me in the right direction?
Thanks


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("sheet2!$I$51") = "" Then
Range("sheet1!$A$641:$A$653").EntireRow.Hidden = True
Else
Range("sheet1!$A$641:$A$653").EntireRow.Hidden = False
End If
If Range("sheet2!$I$52") = "" Then
Range("sheet1!$A$654:$A$666").EntireRow.Hidden = True
Else
Range("sheet1!$A$654:$A$666").EntireRow.Hidden = False
End If
End Sub

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Goto ws_exit
Application.EnableEvenets = True

If Not Intersect(Target,Me.Range("I51:I52")) Is Nothing Then

With Target
Select Case True
Case .Address(0,0) = "I51" And .Value = ""
Worksheets("sheet1").Rows("641:653").Hidden = True
Case .Address(0,0) = "I51" And .Value <> ""
Worksheets("sheet1").Rows("641:653").Hidden = False
Case .Address(0,0) = "I52" And .Value = ""
Worksheets("sheet1").Rows("654:666").Hidden = True
Case .Address(0,0) = "I52" And .Value <> ""
Worksheets("sheet1").Rows("654:666").Hidden = False
End Select
End With
End If

ws_exit:
Application.EnableEvenets = True
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JLGWhiz]

You were mixing Excel formula syntax with VBA syntax. The revised code below
can be put in the code module of the sheet that you want to trigger the macro
(Looks like Sheet2) by right clicking the sheet tab and then select view
code. The problem with using this code is that it will fire every time any
change is made to the worksheet. So look at the second code which restricts
the execution of the code to only the changes in cells I51 and I52

Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("Sheet2").Range("$I$51") = "" Then
Worksheets("Sheet1").Range("$A$641:$A$653").EntireRow.Hidden = True
Else
Worksheets("Sheet1").Range("$A$641:$A$653").EntireRow.Hidden = False
End IF
If Worksheets("Sheet2") .Range("$I$52") = "" Then
Worksheets("Sheet1").Range("$A$654:$A$666").EntireRow.Hidden = True
Else
Worksheets("Sheet1").Range("$A$654:$A$666").EntireRow.Hidden = False
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("Sheet2").Range("$I$51") = "" Then
Worksheets("Sheet1").Range("$A$641:$A$653").EntireRow.Hidden = True
Else
Worksheets("Sheet1").Range("$A$641:$A$653").EntireRow.Hidden = False
End IF
If Worksheets("Sheet2") .Range("$I$52") = "" Then
Worksheets("Sheet1").Range("$A$654:$A$666").EntireRow.Hidden = True
Else
Worksheets("Sheet1").Range("$A$654:$A$666").EntireRow.Hidden = False
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Worksheets("Sheet2").Range("$I$51" Or _
Target = Worksheets("Sheet2").Range("$I$52") Then
If Worksheets("Sheet2").Range("$I$51") = "" Then
Worksheets("Sheet1").Range("$A$641:$A$653").EntireRow.Hidden = True
Else
Worksheets("Sheet1").Range("$A$641:$A$653").EntireRow.Hidden = False
End IF
If Worksheets("Sheet2") .Range("$I$52") = "" Then
Worksheets("Sheet1").Range("$A$654:$A$666").EntireRow.Hidden = True
Else
Worksheets("Sheet1").Range("$A$654:$A$666").EntireRow.Hidden = False
End If
End If
End Sub

 

[jack]

I have 60 cells (I2:I61) on sheet2 each referring to different groups of
rows on sheet1. Is there, possibly, a more compact way rather than repeating
the IF / THEN / ELSE for 60 times in the VBA code. If not, I'll work thru
it.
Thanks

 

[carlo]

Hi Jack

is there any pattern we could use to write the code more compact?

Give us an example of your ranges:
sheet2!I2 = sheet1!rows28:45
(of course not all 60, just 3 or 4)

if there is no pattern you could use a hidden sheet, were you
put in your range matrix

hth
Carlo

 

[jack]

Hi Carlo,
Here's the pattern that I have started. Sheet2 range is I2:I61 . The hide /
unhide rows are A3 thru A782 on sheet1. That is, 13 rows in a group to be
hidden /unhidden for each cell in range I2:I61.
I hope this makes sense.
I'm unsure what you meant by "if there is no pattern you could use a hidden
sheet, were you put in your range matrix". Could you expand on that a bit?
Jack

' If Worksheets("Sheet2").Range("$I$2") = "" Then
' Worksheets("Sheet1").Range("$A$3:$A$15").EntireRow.Hidden = True
' Else
' Worksheets("Sheet1").Range("$A$3:$A$15").EntireRow.Hidden = False
' End If
' If Worksheets("Sheet2").Range("$I$3") = "" Then
' Worksheets("Sheet1").Range("$A$16:$A$28").EntireRow.Hidden = True
' Else
' Worksheets("Sheet1").Range("$A$16:$A$28").EntireRow.Hidden = False
' End If
' If Worksheets("Sheet2").Range("$I$4") = "" Then
' Worksheets("Sheet1").Range("$A$29:$A$41").EntireRow.Hidden = True
' Else
' Worksheets("Sheet1").Range("$A$29:$A$41").EntireRow.Hidden = False
' End If
' If Worksheets("Sheet2").Range("$I$5") = "" Then
' Worksheets("Sheet1").Range("$A$42:$A$54").EntireRow.Hidden = True
' Else
' Worksheets("Sheet1").Range("$A$42:$A$54").EntireRow.Hidden = False
' End If

 

[JLGWhiz]

Hi Jack, give this a try. It should run the 61 cells in sheet 2 and check
each group in sheet 1 to either hide or unhide.

Sub hdUnhd()
Dim i, j, k As Long
For i = 2 To 62
j = 3
k = 15
If Worksheets("Sheet2").Range("I" & i) = "" Then
Worksheets("Sheet1").Range("A" & j & ":A" & k).EntireRow.Hidden =
True
Else
Worksheets("Sheet1").Range("A" & J & ":A" & k).EntireRow.Hidden =
False
End If
j = j + 13
k = k + 13
Next i
End Sub

 

[jack]

JLGWhiz,
I thought your suggestion should work. However, when all the 61 cells in
sheet 2 are empty, and the code is run, only the first group of rows in
sheet 1 become hidden. All the other rows remain unhidden. It's not what I
expected. I expected all the associated rows on sheet 1 would become hidden
and that didn't happen.
I don't understand why this isn't working.
Any suggestions?
Jack

 

[jack]

JLGWhiz,
Sorry, I was too quick to ask for additional help. After looking a little
further, I found the problem. Initilization of "j" & "k" values needed to be
outside the FOR / NEXT loop (as shown below). Otherwise, the code just
re-starts with the initilization values. I also had to expand it to check 71
cells.
Thanks for your help, the code does just what I wanted!
I'm slowly learning with all the help from the discussion group input.
Thanks again!
Jack


Sub hdUnhd()
Dim i, j, k As Long
j = 3
k = 15
For i = 2 To 73
If Worksheets("Sheet2").Range("I" & i) = "" Then
Worksheets("Sheet1").Range("A" & j & ":A" & k).EntireRow.Hidden =
True
Else
Worksheets("Sheet1").Range("A" & j & ":A" & k).EntireRow.Hidden =
False
End If
j = j + 13
k = k + 13
Next i
End Sub