The code works perfectly. If you've got a fan club then sign me up!

"Gary''s Student" wrote:

Let's add another macro just above the first one:

Private Sub Worksheet_Activate()
Range("F2").Value = ""
Rows("3:30").EntireRow.Hidden = True
End Sub


They must both be in worksheet code, of course.
--
Gary's Student


"Rajat" wrote:

Dear "Gary''s Student"

thanks a lot for your help. The code did the work what i was trying to do.
But few fine tuining required, Those are -

When the file open by default cell F2 value is "" (Blank) and at that time
or untill unless the value is selected (from 1 to 7 in the drop down list of
cell F2) upto that time the Row 3:30 remain to be hidden.

thanks again for your help,

Regards
Rajat

"Gary''s Student" wrote:

Put this in worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F2")) Is Nothing Then
Exit Sub
End If

Rows("3:30").EntireRow.Hidden = False
hide_um = Array("7:30", "11:30", "15:30", "19:30", "23:30", "27:30")
v = Range("F2").Value
If v = 7 Then
Exit Sub
End If
Rows(hide_um(v - 1)).EntireRow.Hidden = True
End Sub
--
Gary''s Student


"Rajat" wrote:

Im using Excel 2003, what I intended to accomplish to create a macro to
unhide row (By default few rows will be hidden) based on the drop down list
value of a Cell.

Say I have created a dropdown list in F2 cell using DataValidationList
which have value 1 to 7.
I need the Macro to do the following €“
1. When the sheet is open or Active hide Row 3 to 30
2. If the Cell F2 Value is selected 1 then immediately Row 3 to 6 will be
unhide/Shown but rows 7 to 30 will be hidden.
3. If the Cell F2 Value is selected 2 then immediately Row 3 to 10 will be
unhide/Shown but rows 11 to 30 will be hidden.
4. If the Cell F2 Value is selected 3 then immediately Row 3 to 14 will be
unhide/Shown but rows 15 to 30 will be hidden.
5. If the Cell F2 Value is selected 4 then immediately Row 3 to 18 will be
unhide/Shown but rows 19 to 30 will be hidden.
6. If the Cell F2 Value is selected 5 then immediately Row 3 to 22 will be
unhide/Shown but rows 23 to 30 will be hidden.
7. If the Cell F2 Value is selected 6 then immediately Row 3 to 26 will be
unhide/Shown but rows 27 to 30 will be hidden.
8. If the Cell F2 Value is selected 7 then immediately Row 3 to 30 will be
unhide/Shown.

Thanks in advance,