Unhide roes in a different worksheet - My code is not working

[Ram]

Hi,

Basing on values 1to 5 given in a dropdown, I want different rows to
unhide and hide accordingly. I have this code it does not work at
all!!!!

Private Sub Page()
Dim conf
conf = Sheet1.Range("Confirmation").Value

If conf = 1 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
End If

If conf = 2 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
End If
If conf = 3 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
End If
If conf = 4 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
Sheet6.Rows("134:136").EntireRow.Hidden = False
End If
If conf = 5 Then
Sheet6.Rows("4:6").EntireRow.Hidden = False
Sheet6.Rows("48:50").EntireRow.Hidden = False
Sheet6.Rows("91:93").EntireRow.Hidden = False
Sheet6.Rows("134:136").EntireRow.Hidden = False
Sheet6.Rows("178:180").EntireRow.Hidden = False
End If

If conf = "Select one" Then
Sheet6.Rows("4:6").EntireRow.Hidden = True
Sheet6.Rows("48:50").EntireRow.Hidden = True
Sheet6.Rows("91:93").EntireRow.Hidden = True
Sheet6.Rows("134:136").EntireRow.Hidden = True
Sheet6.Rows("178:180").EntireRow.Hidden = True
End If

End Sub

Some one please help me make modifications to this code or give me
another work around code.
Thanks a lot
Regards,
SRC

 

[Gary Keramidas]

give this a try

Private Sub Page()
Dim ws As Worksheet
Dim ws6 As Worksheet
Dim conf As Variant
Set ws = Worksheets("Sheet1")
Set ws6 = Worksheets("Sheet6")
conf = ws.Range("Confirmation").Value

Select Case conf

Case 1
ws6.Rows("4:6").EntireRow.Hidden = False

Case 2
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False

Case 3
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False
ws6.Rows("91:93").EntireRow.Hidden = False

Case 4
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False
ws6.Rows("91:93").EntireRow.Hidden = False
ws6.Rows("134:136").EntireRow.Hidden = False

Case 5
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False
ws6.Rows("91:93").EntireRow.Hidden = False
ws6.Rows("134:136").EntireRow.Hidden = False
ws6.Rows("178:180").EntireRow.Hidden = False

Case Else
If LCase(conf) = "select one" Then
ws6.Rows("4:6").EntireRow.Hidden = True
ws6.Rows("48:50").EntireRow.Hidden = True
ws6.Rows("91:93").EntireRow.Hidden = True
ws6.Rows("134:136").EntireRow.Hidden = True
ws6.Rows("178:180").EntireRow.Hidden = True
End If
End Select
End Sub

 

[Ram]

give this a try

Private Sub Page()
Dim ws As Worksheet
Dim ws6 As Worksheet
Dim conf As Variant
Set ws = Worksheets("Sheet1")
Set ws6 = Worksheets("Sheet6")
conf = ws.Range("Confirmation").Value

Select Case conf

Case 1
ws6.Rows("4:6").EntireRow.Hidden = False

Case 2
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False

Case 3
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False
ws6.Rows("91:93").EntireRow.Hidden = False

Case 4
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False
ws6.Rows("91:93").EntireRow.Hidden = False
ws6.Rows("134:136").EntireRow.Hidden = False

Case 5
ws6.Rows("4:6").EntireRow.Hidden = False
ws6.Rows("48:50").EntireRow.Hidden = False
ws6.Rows("91:93").EntireRow.Hidden = False
ws6.Rows("134:136").EntireRow.Hidden = False
ws6.Rows("178:180").EntireRow.Hidden = False

Case Else
If LCase(conf) = "select one" Then
ws6.Rows("4:6").EntireRow.Hidden = True
ws6.Rows("48:50").EntireRow.Hidden = True
ws6.Rows("91:93").EntireRow.Hidden = True
ws6.Rows("134:136").EntireRow.Hidden = True
ws6.Rows("178:180").EntireRow.Hidden = True
End If
End Select
End Sub

--

Gary













- Show quoted text -

Hi Gary,

Thanks a bunch, this works just the way i wanted.

Regards,
SRC

 

[NickHK]

If you can change you design slightly to give a constant spacing between
these sections (instead of the 43/44 that you have at the moment), you could
do it in a loop:

Private Sub CommandButton2_Click()
Const SPACING As Long = 44

ShowHide Sheet1.Range("Confirmation").Value, SPACING

End Sub

Private Sub ShowHide(SectionCount As Variant, RowSpacing As Long)
Dim i As Long
Dim ShowRows As Boolean
Dim Counter As Long

If IsNumeric(SectionCount) Then
Counter = SectionCount
ShowRows = False
Else
Counter = 5
ShowRows = True
End If

For i = 1 To Counter
Sheet6.Rows(4 + RowSpacing * (i - 1) & ":" & 6 + RowSpacing * (i -
1)).EntireRow.Hidden = ShowRows
'Debug.Print Sheet6.Rows(4 + RowSpacing * (i - 1) & ":" & 6 + RowSpacing
* (i - 1)).Address
Next

End Sub

NickHK