Code to hide and unhide cells (SC).

[sreeram.i.c]

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.

 

[JE McGimpsey]

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

 

[sreeram.i.c]

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





- Show quoted text -

Thanks a lot this works just as i wanted.

 

[Ram]

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


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.

 

[Norman Jones]

Hi Ram,

See at least one response il your later thread.


---
Regards,
Norman