Home

Re: Excel Hide Sheets Based on A Cell Value

%3CLINGO-SUB%20id%3D%22lingo-sub-751492%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Hide%20Sheets%20Based%20on%20A%20Cell%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-751492%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3BThis%20works%20perfectly!%20Thank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20last%20one%20you%20may%20be%20able%20to%20help%20me%20with.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20hide%20rows%20on%20a%20sheet%20based%20on%20a%20number%20in%20a%20cell%20(A6).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStarting%20again%20with%20row%209%20being%20the%20first%20row%2C%20so%20if%20number%201%20is%20in%20the%20A6%20cell%20then%20row%209%20is%20shown%2C%3C%2FP%3E%3CP%3Eif%20number%202%20is%20in%20A6%20then%20rows%209%20and%2010%20are%20shown.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EContinuing%20up%20to%20number%20250%20in%20A6%20showing%20all%20rows%20up%20to%20row%20258.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20rows%201-8%20and%20all%20rows%20after%20258%20always%20visible%20and%20not%20effected%20but%20this%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20not%20completed%20a%20function%20like%20this%20before%20and%20am%20not%20having%20much%20luck%20on%20Google.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753232%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Hide%20Sheets%20Based%20on%20A%20Cell%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753232%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375132%22%20target%3D%22_blank%22%3E%40AB_2019%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20do%20what%20you%20request%20using%20VBA%20code.%20The%20suggested%20sub%20runs%20automatically%20when%20you%20change%20the%20value%20of%20cell%20A6.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMake%20sure%20you%20put%20the%20sub%20in%20the%20code%20pane%20for%20the%20worksheet%20being%20watched.%20Rightclick%20its%20sheet%20tab%2C%20choose%20View%20Code...%20and%20paste%20the%20sub%20there.%20Make%20sure%20you%20save%20the%20workbook%20with%20.xls%2C%20.xlsm%20or%20.xlsb%20file%20extension.%3C%2FP%3E%0A%3CPRE%3E'Put%20this%20sub%20in%20the%20code%20pane%20for%20the%20worksheet%20being%20watched%0APrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0ADim%20targ%20As%20Range%2C%20watch%20As%20Range%0ASet%20targ%20%3D%20Me.Range(%229%3A258%22)%0ASet%20watch%20%3D%20Me.Range(%22A6%22)%0AIf%20Not%20Intersect(watch%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20On%20Error%20Resume%20Next%0A%20%20%20%20targ.EntireRow.Hidden%20%3D%20True%0A%20%20%20%20targ.Resize(watch.Value).EntireRow.Hidden%20%3D%20False%0AEnd%20If%0AEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754093%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Hide%20Sheets%20Based%20on%20A%20Cell%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754093%22%20slang%3D%22en-US%22%3E%3CP%3EPerfect!%20thank%20you%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
AB_2019
Occasional 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.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies