Hiding "n" number of rows when "n" is selected in a cell

[bigexcel]

hi there.
i am making a form in excel.
and one of the entries is "number of server" - this can be 0 1 2 3.

When 0 is selected I want the successive rows of "Server1", "Server2" and "Server3" hidden.

When 1 is selected, I will see only "Server1" and so on.

I have no background in Excel or VB programming and below is what I could gather from the web.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C17")) Is Nothing Then _
        Rows("18:19").Hidden = Not (Range("C17") = "Yes")
        
    If Not Intersect(Target, Range("C10")) Is Nothing Then _
    Rows("11:13").Hidden = (Range("C10") = "0")
    
    If Not Intersect(Target, Range("C10")) Is Nothing Then _
    Rows("11:11").Hidden = Not (Range("C10") = "1")
    
    If Not Intersect(Target, Range("C10")) Is Nothing Then _
    Rows("13:13").Hidden = (Range("C10") = "2")
End Sub

there is another cell on which another 2 rows are hidden, so thats the first 3 lines of the code.

remaining code is for detecting C10 value which is what i am looking for now.

any help will be appreciated.
thanks

 

[bigexcel]

anyone?

 

[Ralajer]

Welcome to the board bigexcel. If I am understanding your requirements correctly you want to hide/show as follows

• C10 = 0 — Hide rows 11-13
• C10 = 1 — Hide rows 11-12
• C10 = 2 — Hide rows 11
• C10 = 3 — Hide rows none

The following code will do that.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("C10")) Is Nothing Then
        Dim numServers As Integer
        Dim totServers As Integer
        Dim i As Integer
        Dim baseRow As Range
    
        Set baseRow = Rows("11:11")
        numServers = Range("C10").Value
        totServers = 3
        
        If numServers >= 0 And numServers <= totServers Then
            '// Screen is not update at each step
            Application.ScreenUpdating = False
            
            '// Unhide all server rows
            Range(baseRow, baseRow.Offset(totServers - 1, 0)).Hidden = False
            i = 0
            '// Loop will Unhiding rows starting last row(base row + total - 1)
            '// For the number of rows to hide (Total - Show)
            Do While i < (totServers - numServers)
                i = i + 1
                baseRow.Offset(totServers - i, 0).Hidden = True
            Loop
            '// Turn Screen updating on
            Application.ScreenUpdating = True
            
        Else
            '// Optional/Testing Message if input out range
            ' MsgBox "The Number of servers is between 0 and " & totServers
        End If
    End If
End Sub

 

[Ralajer]

Slightly simpler method

Private Sub Worksheet_Change(ByVal Target As Range)
'// ———————————————————————————————————————————————————————————————————————————
'// Range C10
    If Not Intersect(Target, Range("C10")) Is Nothing Then
        Dim numServers As Integer
        Dim totServers As Integer
        Dim baseRow As Range
    
        Set baseRow = Rows("11:11")
        numServers = Range("C10").Value
        totServers = 3
        
        If numServers >= 0 And numServers <= totServers Then           
            '// Unhide all server rows
            Range(baseRow, baseRow.Offset(totServers - 1, 0)).Hidden = False
            If numServers <> totServers Then
                '// Hide rows
                Range(baseRow.Offset(numServers, 0), baseRow.Offset(totServers - 1, 0)).Hidden = True
            End If
        Else
            '// Optional/Testing Message if input out range
            ' MsgBox "The Number of servers is between 0 and " & totServers
        End If
    End If
End Sub

 

[bigexcel]

thanks guys.
when i came to office today and checked the file, magically it is working exactly as i want it.

C10 = 0 hide 11 to 13
C10 = 1 hide 12 to 13
C10 = 2 hide 13 to 13
C10 = 3 hide nothing

this is the code i have now:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C17")) Is Nothing Then _
        Rows("18:19").Hidden = Not (Range("C17") = "Yes")
        
        
        If Not Intersect(Target, Range("C10")) Is Nothing Then _
        Rows("11:13").Hidden = (Range("C10") = "0")
    
    If Not Intersect(Target, Range("C10")) Is Nothing Then _
        Rows("12:13").Hidden = (Range("C10") = "1")
    
    If Not Intersect(Target, Range("C10")) Is Nothing Then _
        Rows("13:13").Hidden = (Range("C10") = "2")
 
If Cells(10, "C").Value = "0" Then Rows("11:13").Hidden = True
If Cells(10, "C").Value = "1" Then Rows("12:13").Hidden = True
If Cells(10, "C").Value = "2" Then Rows("13:13").Hidden = True
End Sub

does this code look crazy or just wrong? im kind of surprised that its working. maybe it needs refinement?

i will check out the code that you guys have pasted too.
thanks.

 

[Ralajer]

The code except for what I commented on below is fine but it is not very flexible. Meaning if you wanted to move the Server rows, change the number of servers, or change the cell for holding the number of servers to display you would have to adjust most if not all of the lines. As opposed to my approach you only have to change the 4 lines. Of course if does what you want it to that enough in a lot cases.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C17")) Is Nothing Then _
        Rows("18:19").Hidden = Not (Range("C17") = "Yes")
        
        If Not Intersect(Target, Range("C10")) Is Nothing Then _
        Rows("11:13").Hidden = (Range("C10") = "0")
    
    If Not Intersect(Target, Range("C10")) Is Nothing Then _
        Rows("12:13").Hidden = (Range("C10") = "1")
    
    If Not Intersect(Target, Range("C10")) Is Nothing Then _
        Rows("13:13").Hidden = (Range("C10") = "2")

[B]'// The below does the same thing as the 3 above if I am interpreting it correctly
[/B][COLOR="Red"] 
'// The problem with this that it run every time the sheet is changed which
'// isn't a big problem but it better to run it only when C10 changes.
If Cells(10, "C").Value = "0" Then Rows("11:13").Hidden = True
If Cells(10, "C").Value = "1" Then Rows("12:13").Hidden = True
If Cells(10, "C").Value = "2" Then Rows("13:13").Hidden = True[/COLOR]
[COLOR="SeaGreen"]    
    '// This will do that
    If Not Intersect(Target, Range("C10")) Is Nothing Then
       If Cells(10, "C").Value = "0" Then Rows("11:13").Hidden = True
       If Cells(10, "C").Value = "1" Then Rows("12:13").Hidden = True
       If Cells(10, "C").Value = "2" Then Rows("13:13").Hidden = True
    End If[/COLOR]
End Sub