I'm designing a form where Cell A5, which is named Off_Num. This cell
has Values (Select One, 1 to 20) in a dropdown, and Rows 9 to 28 are
hidden.

Now if 1 is selected in Off_Num cell then, the code should Unhide Row
9. And if 2 is selected then Row(9:10) should be unhidden so on, till
if 20 is selected the Rows(9:28) should be unhidden. And if Select One
option is selected that Rows(9:28) should get hidden again.

Please help me with this.

One way:

Put this in your worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Range("Off_Num")
If Not Intersect(.Cells, Target) Is Nothing Then
Application.ScreenUpdating = False
Rows("9:28").Hidden = True
If IsNumeric(.Value) Then
Rows("9:9").Resize(CLng(.Value)).Hidden = False
End If
Application.ScreenUpdating = True
End If
End With
End Sub


In article <(E-Mail Removed)>,
(E-Mail Removed) wrote:

> I'm designing a form where Cell A5, which is named Off_Num. This cell
> has Values (Select One, 1 to 20) in a dropdown, and Rows 9 to 28 are
> hidden.
>
> Now if 1 is selected in Off_Num cell then, the code should Unhide Row
> 9. And if 2 is selected then Row(9:10) should be unhidden so on, till
> if 20 is selected the Rows(9:28) should be unhidden. And if Select One
> option is selected that Rows(9:28) should get hidden again.
>
> Please help me with this.

On Mar 31, 7:06 pm, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> One way:
>
> Put this in your worksheet code module:
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> With Range("Off_Num")
> If Not Intersect(.Cells, Target) Is Nothing Then
> Application.ScreenUpdating = False
> Rows("9:28").Hidden = True
> If IsNumeric(.Value) Then
> Rows("9:9").Resize(CLng(.Value)).Hidden = False
> End If
> Application.ScreenUpdating = True
> End If
> End With
> End Sub
>
> In article <1175337624.979491.56...@o5g2000hsb.googlegroups.com>,
>
>
>
> sreeram....@gmail.com wrote:
> > I'm designing a form where Cell A5, which is named Off_Num. This cell
> > has Values (Select One, 1 to 20) in a dropdown, and Rows 9 to 28 are
> > hidden.
>
> > Now if 1 is selected in Off_Num cell then, the code should Unhide Row
> > 9. And if 2 is selected then Row(9:10) should be unhidden so on, till
> > if 20 is selected the Rows(9:28) should be unhidden. And if Select One
> > option is selected that Rows(9:28) should get hidden again.
>
> > Please help me with this.- Hide quoted text -
>
> - Show quoted text -

Thanks a lot this works just as i wanted.

On Mar 31, 10:53 pm, sreeram....@gmail.com wrote:
> On Mar 31, 7:06 pm, JE McGimpsey <jemcgimp...@mvps.org> wrote:
>
>
>
>
>
> > One way:
>
> > Put this in your worksheet code module:
>
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > With Range("Off_Num")
> > If Not Intersect(.Cells, Target) Is Nothing Then
> > Application.ScreenUpdating = False
> > Rows("9:28").Hidden = True
> > If IsNumeric(.Value) Then
> > Rows("9:9").Resize(CLng(.Value)).Hidden = False
> > End If
> > Application.ScreenUpdating = True
> > End If
> > End With
> > End Sub
>
> > In article <1175337624.979491.56...@o5g2000hsb.googlegroups.com>,
>
> > sreeram....@gmail.com wrote:
> > > I'm designing a form where Cell A5, which is named Off_Num. This cell
> > > has Values (Select One, 1 to 20) in a dropdown, and Rows 9 to 28 are
> > > hidden.
>
> > > Now if 1 is selected in Off_Num cell then, the code should Unhide Row
> > > 9. And if 2 is selected then Row(9:10) should be unhidden so on, till
> > > if 20 is selected the Rows(9:28) should be unhidden. And if Select One
> > > option is selected that Rows(9:28) should get hidden again.
>
> > > Please help me with this.- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks a lot this works just as i wanted.- Hide quoted text -
>
> - Show quoted text -

Now i am back with another question

How can i write the same code if i have to unhide a set of rows
instead of a single row. Similar to above, if 1 is selected in cell
Mod_Num then rows (5 to 10) should get unhidden and in 2 is selected
rows (5 to 15) will get unhidden so on till i enter 5 to unhide the
entire range of rows that are hidden. is this possible??
Thanks for your help.

Hi Ram,

See at least one response il your later thread.


---
Regards,
Norman



"Ram" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mar 31, 10:53 pm, sreeram....@gmail.com wrote:
>> On Mar 31, 7:06 pm, JE McGimpsey <jemcgimp...@mvps.org> wrote:
>>
>>
>>
>>
>>
>> > One way:
>>
>> > Put this in your worksheet code module:
>>
>> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>> > With Range("Off_Num")
>> > If Not Intersect(.Cells, Target) Is Nothing Then
>> > Application.ScreenUpdating = False
>> > Rows("9:28").Hidden = True
>> > If IsNumeric(.Value) Then
>> > Rows("9:9").Resize(CLng(.Value)).Hidden = False
>> > End If
>> > Application.ScreenUpdating = True
>> > End If
>> > End With
>> > End Sub
>>
>> > In article <1175337624.979491.56...@o5g2000hsb.googlegroups.com>,
>>
>> > sreeram....@gmail.com wrote:
>> > > I'm designing a form where Cell A5, which is named Off_Num. This cell
>> > > has Values (Select One, 1 to 20) in a dropdown, and Rows 9 to 28 are
>> > > hidden.
>>
>> > > Now if 1 is selected in Off_Num cell then, the code should Unhide Row
>> > > 9. And if 2 is selected then Row(9:10) should be unhidden so on, till
>> > > if 20 is selected the Rows(9:28) should be unhidden. And if Select
>> > > One
>> > > option is selected that Rows(9:28) should get hidden again.
>>
>> > > Please help me with this.- Hide quoted text -
>>
>> > - Show quoted text -
>>
>> Thanks a lot this works just as i wanted.- Hide quoted text -
>>
>> - Show quoted text -
>
> Now i am back with another question
>
> How can i write the same code if i have to unhide a set of rows
> instead of a single row. Similar to above, if 1 is selected in cell
> Mod_Num then rows (5 to 10) should get unhidden and in 2 is selected
> rows (5 to 15) will get unhidden so on till i enter 5 to unhide the
> entire range of rows that are hidden. is this possible??
> Thanks for your help.
>