Re: Excel Hide Sheets Based on A Cell Value

Copper Contributor

@Ingeborg Hawighorst This works perfectly! Thank you.

 

One last one you may be able to help me with.

 

I would like to hide rows on a sheet based on a number in a cell (A6).

 

Starting again with row 9 being the first row, so if number 1 is in the A6 cell then row 9 is shown,

if number 2 is in A6 then rows 9 and 10 are shown.

 

Continuing up to number 250 in A6 showing all rows up to row 258.

 

I would like rows 1-8 and all rows after 258 always visible and not effected but this formula.

 

I have not completed a function like this before and am not having much luck on Google.

 

Thank you.

2 Replies

@AB_2019 

You can do what you request using VBA code. The suggested sub runs automatically when you change the value of cell A6.

 

Make sure you put the sub in the code pane for the worksheet being watched. Rightclick its sheet tab, choose View Code... and paste the sub there. Make sure you save the workbook with .xls, .xlsm or .xlsb file extension.

'Put this sub in the code pane for the worksheet being watched
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range, watch As Range
Set targ = Me.Range("9:258")
Set watch = Me.Range("A6")
If Not Intersect(watch, Target) Is Nothing Then
    On Error Resume Next
    targ.EntireRow.Hidden = True
    targ.Resize(watch.Value).EntireRow.Hidden = False
End If
End Sub

Perfect! thank you for your help.