Code to hide and unhide cells (SC).
 

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.

Code to hide and unhide cells (SC).
 

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 om,
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.

Code to hide and unhide cells (SC).
 

On Mar 31, 7:06 pm, JE McGimpsey 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 om,



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.

Code to hide and unhide cells (SC).
 

On Mar 31, 10:53 pm, wrote:
On Mar 31, 7:06 pm, JE McGimpsey 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 om,

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.

Code to hide and unhide cells (SC).
 

Hi Ram,

See at least one response il your later thread.


---
Regards,
Norman



"Ram" wrote in message
oups.com...
On Mar 31, 10:53 pm, wrote:
On Mar 31, 7:06 pm, JE McGimpsey 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 om,

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.