Hide rows automatically

**wwblade** · 05-06-2012, 07:05 PM

Hi, first time on the forum.

I have an excel 2003 spreadsheet and would like to hide different ranges of rows based on the value of one cell.

If C3 = 12 then hide rows 26 to 104
If C3 = 11 then hide rows 5 to 24 and rows 49 to 104
IF C3 = 10 then hide rows 5 to 43 and rows 71 to 104
IF C3 = 9 then hide rows 5 to 65 and rows 90 to 104
IF C3 = 8 then hide rows 5 to 86

Worksheet is called "UNDER 15's Knock out"

Any help would be much appreciated.

Thanks

**JBeaucaire** · 05-06-2012, 07:16 PM

You are making manual changes to cell C3? If so, you can put a macro into the sheet module to specifically watch these manual changes to cell C3 and hide/unhide rows based on the current value. Right-click the tab and select View Code, paste in this sample code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, [C3]) Is Nothing Then
    Range("5:104").EntireRow.Hidden = False

    Select Case [C3].Value
        Case 12:    Range("26:104").EntireRow.Hidden = True
        Case 11:    Range("5:24, 49:104").EntireRow.Hidden = True
        Case 10:    Range("5:43, 71:104").EntireRow.Hidden = True
        Case 9:     Range("5:65, 90:104").EntireRow.Hidden = True
        Case 8:     Range("5:86").EntireRow.Hidden = True
    End Select
End If

End Sub

**wwblade** · 05-07-2012, 02:57 PM

Thanks the Cell C£ is calculated by the addition of two Cells in a different worksheet.

**wwblade** · 05-07-2012, 02:55 PM

Thanks, the value in Cell 3 is calculated by the addition of two pother cells in a different worksheet.

**JBeaucaire** · 05-07-2012, 06:30 PM

Like so, then:

Private Sub Worksheet_Calculate()
    Range("5:104").EntireRow.Hidden = False

    Select Case [C3].Value
        Case 12:    Range("26:104").EntireRow.Hidden = True
        Case 11:    Range("5:24, 49:104").EntireRow.Hidden = True
        Case 10:    Range("5:43, 71:104").EntireRow.Hidden = True
        Case 9:     Range("5:65, 90:104").EntireRow.Hidden = True
        Case 8:     Range("5:86").EntireRow.Hidden = True
    End Select
End Sub