Forum Discussion
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
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 SubBest regards
Bernd
https://vba-tanker.com/
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.
Hi,
macros are activated?Copy the macro to a new workbook behind first worksheet and test again
