Code to Unhide Rows using Macro

[Rajat]

I’m 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 Data>Validation>List
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,

 

[Don Guillett]

Sub hiderows()'assuming 1,2,3,4 entered into A2
sub hiderow()
Cells.EntireRow.Hidden = False
x = 2 + Range("a2") * 4
Rows("3:" & x).Hidden = True
End Sub

 

[Don Guillett]

If you need it to be automatic, look here
http://www.contextures.com/xlDataVal08.html#Change

 

[Gary''s Student]

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

 

[Rajat]

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]

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.

 

[Rajat]

Dear "Gary''s Student",

the code worked well.
But i have another probles -
is it possible to create another

"Private Sub Worksheet_Change" event

because i need to create a same row hiding procedure as described in earlier
post now in cell F37 which drop down value 1 to 7 will hide cell 38:42 to
62:66 respectively according to the value selected.

Can you suggest me what to do ...

Regards

Rajat

 

[Dave The Favorite]

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