# Hide and Show Rows in a Sheet Based on a cell value

Highlighted
Occasional Contributor

# Hide and Show Rows in a Sheet Based on a cell value

Hi All,

Hoping someone can help me out.

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

Starting with row 9 first, so if number 1 is in the A6 cell then row 9 is shown and rows 10 to 258 are hidden.

If number 2 is in A6 then rows 9 and 10 are shown and rows 11 to 258 are hidden.

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

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!

5 Replies
Highlighted

# Re: Hide and Show Rows in a Sheet Based on a cell value

Hi,

you can use the following event, see attached example, too.

`Private Sub Worksheet_Change(ByVal Target As Range)Dim rngArea As RangeIf Target.Address <> "\$A\$6" Then Exit SubRows.Hidden = FalseSelect Case Target.ValueCase 1Set rngArea = Range(Rows(10), Rows(Rows.Count))rngArea.Hidden = TrueCase 2Set rngArea = Range(Rows(11), Rows(Rows.Count))rngArea.Hidden = TrueCase 3Set rngArea = Range(Rows(12), Rows(Rows.Count))rngArea.Hidden = TrueCase 4Set rngArea = Range(Rows(13), Rows(Rows.Count))rngArea.Hidden = TrueCase 5Set rngArea = Range(Rows(14), Rows(Rows.Count))rngArea.Hidden = TrueCase 6Set rngArea = Range(Rows(15), Rows(Rows.Count))rngArea.Hidden = TrueCase 7Set rngArea = Range(Rows(16), Rows(Rows.Count))rngArea.Hidden = TrueCase 8Set rngArea = Range(Rows(17), Rows(Rows.Count))rngArea.Hidden = TrueCase ElseSet rngArea = Range(Rows(8), Rows(Rows.Count))rngArea.Hidden = FalseEnd SelectEnd Sub`

Best regards

Bernd

the vba-tanker - a databse full of  macros

Highlighted

# Re: Hide and Show Rows in a Sheet Based on a cell value

Hi Bernd,

I am not sure if I am using this correctly, I have downloaded your example and have put some different numbers in the highlighted A6 cell but it does not have any effect on the sheet, all rows remain visible.

Can you please let me know if i am missing something.

Highlighted

# Re: Hide and Show Rows in a Sheet Based on a cell value

Hi,
macros are activated?

Copy the macro to a new workbook behind first worksheet and test again

Highlighted

# Re: Hide and Show Rows in a Sheet Based on a cell value

Hi, Yes they are on. I have seen it work in a new spreadsheet.

to

I would like to try a different approach to this one.

Can you please let me know if it is possible to create a macro to apply to an active X toggle button to show and hide rows.

The values are "Yes" to show the row and "No" to hide the row.

These are in column A rows 9 to 258.

Thank you.

Highlighted

# Re: Hide and Show Rows in a Sheet Based on a cell value

Hi, that's a good idea. Look at the attachment.

`Private Sub ToggleButton1_Click()Dim rngArea As RangeSet rngArea = Range(Rows(9), Rows(258))If Me.ToggleButton1.Value = True ThenrngArea.Hidden = FalseMe.ToggleButton1.Caption = "Hide"ElserngArea.Hidden = TrueMe.ToggleButton1.Caption = "Show"End IfEnd Sub`

Best regards

Bernd

My website vba-tanker.com