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

[AB_2019]

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!

[#2]

@AB_2019 

Hi,

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

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngArea As Range

If Target.Address <> "$A$6" Then Exit Sub
Rows.Hidden = False
Select Case Target.Value
Case 1
Set rngArea = Range(Rows(10), Rows(Rows.Count))
rngArea.Hidden = True

Case 2
Set rngArea = Range(Rows(11), Rows(Rows.Count))
rngArea.Hidden = True

Case 3
Set rngArea = Range(Rows(12), Rows(Rows.Count))
rngArea.Hidden = True

Case 4
Set rngArea = Range(Rows(13), Rows(Rows.Count))
rngArea.Hidden = True

Case 5
Set rngArea = Range(Rows(14), Rows(Rows.Count))
rngArea.Hidden = True

Case 6
Set rngArea = Range(Rows(15), Rows(Rows.Count))
rngArea.Hidden = True

Case 7
Set rngArea = Range(Rows(16), Rows(Rows.Count))
rngArea.Hidden = True

Case 8
Set rngArea = Range(Rows(17), Rows(Rows.Count))
rngArea.Hidden = True

Case Else
Set rngArea = Range(Rows(8), Rows(Rows.Count))
rngArea.Hidden = False
End Select

End Sub

Best regards

Bernd

the vba-tanker - a databse full of  macros

[#3]

@Berndvbatanker 

Hi Bernd,

Thanks for your reply,

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.

[#4]

Hi,
macros are activated?

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

[#5]

@Berndvbatanker 

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.

[#6]

@AB_2019 

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

Private Sub ToggleButton1_Click()
Dim rngArea As Range

Set rngArea = Range(Rows(9), Rows(258))
If Me.ToggleButton1.Value = True Then
rngArea.Hidden = False
Me.ToggleButton1.Caption = "Hide"
Else
rngArea.Hidden = True
Me.ToggleButton1.Caption = "Show"
End If

End Sub

Best regards

Bernd

My website vba-tanker.com