Showing or Hiding Rows based on Subsequent Criteria

Question

Very new to this so apologies for my ignorance. Hopefully this is explained clearly.

I've cobbled together some code that currently hides or shows a range of cells, based on a drop down list of weeks 1 through 5 (i.e. If week 1 is selected, weeks 2-5 are hidden and only cells for week 1 show). Within each week I then have a subsequent drop down selecting options to display rows for the given week, from 1 - 30 rows.

So select a week number to display relevant rows then select a number to select the number of rows shown for given week.

The problem I'm having is that when a given week is selected, all 30 rows for that week are shown again even if previously the user selected to show only rows 1-15.

I understand it's because when a week is selected, only that if statement is checked and the subsequent ones don't run again, but I can't figure out how to rearrange to ensure the subsequent selection is maintained.

I'd like each week to only show the number of rows that the user defined previously, so you can toggle between weeks and see only what was previously selected.

I.e. if a user selects rows 1-15 for week 1, toggles to week 2, and then back to week 1, I only want rows 1-15 to show in week 1, not all 30 rows.

Sample for 'Week 1' below, the sub continues for weeks 2-5 with similar code.

'

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = Range("SELECT_WEEK_FEB").Address Then
        If Target.Value = "Week 1" Then
            Range("WEEK_1_FEB").EntireRow.Hidden = False
            Range("WEEK_2_FEB, WEEK_3_FEB, WEEK_4_FEB, WEEK_5_FEB").EntireRow.Hidden = True
        ElseIf Target.Value = "Week 2" Then
            Range("WEEK_2_FEB").EntireRow.Hidden = False
            Range("WEEK_1_FEB, WEEK_3_FEB, WEEK_4_FEB, WEEK_5_FEB").EntireRow.Hidden = True
        ElseIf Target.Value = "Week 3" Then
            Range("WEEK_3_FEB").EntireRow.Hidden = False
            Range("WEEK_1_FEB, WEEK_2_FEB, WEEK_4_FEB, WEEK_5_FEB").EntireRow.Hidden = True
        ElseIf Target.Value = "Week 4" Then
            Range("WEEK_4_FEB").EntireRow.Hidden = False
            Range("WEEK_1_FEB, WEEK_2_FEB, WEEK_3_FEB, WEEK_5_FEB").EntireRow.Hidden = True
        ElseIf Target.Value = "Week 5" Then
            Range("WEEK_5_FEB").EntireRow.Hidden = False
            Range("WEEK_1_FEB, WEEK_2_FEB, WEEK_3_FEB, WEEK_4_FEB").EntireRow.Hidden = True

        End If
    End If

     '------------------------------------------------------------------------------------------------------
     ' WEEK 1
 If Target.Address = Range("Week_1_Items_Feb").Address Then
        If Target.Value = "1-15" Then
            Range("Feb_Week_1_1_to_15").EntireRow.Hidden = False
            Range("Feb_Week_1_16_to_20, Feb_Week_1_21_to_25,Feb_Week_1_26_to_30").EntireRow.Hidden = True
        ElseIf Target.Value = "16-20" Then
            Range("Feb_Week_1_1_to_15, Feb_Week_1_16_to_20").EntireRow.Hidden = False
            Range("Feb_Week_1_21_to_25, Feb_Week_1_26_to_30").EntireRow.Hidden = True
        ElseIf Target.Value = "21-25" Then
            Range("Feb_Week_1_1_to_15, Feb_Week_1_16_to_20, Feb_Week_1_21_to_25").EntireRow.Hidden = False
            Range("Feb_Week_1_26_to_30").EntireRow.Hidden = True
         ElseIf Target.Value = "26-30" Then
            Range("Feb_Week_1_1_to_15, Feb_Week_1_16_to_20, Feb_Week_1_21_to_25, Feb_Week_1_26_to_30").EntireRow.Hidden = False
        End If
    End If'

user_9808063 · Answer

If you define all week's range together as "All_Week" then code will be short. Then, you can interlink the two criteria by adding one line to each if condition. If you add "All" to "Week_1_Items_Feb" then you can see all the rows for given "SELECT_WEEK_FEB".

Also, I noticed that, you need "Week_1_Items_Feb" to be cumulative. So for 16-20 you need 1-20 output. Then, I would suggest - instead of mentioning it as 16-20 you can mention in like 1-20 in the dropdown list.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("SELECT_WEEK_FEB").Address Then
        Range("All_Week").EntireRow.Hidden = True
        If Range("Week_1_Items_Feb").Value = "All" Then
        Range(Target.Value).EntireRow.Hidden = False
        Else
        Range(Target.Value).Rows(Replace(Range("Week_1_Items_Feb").Value, "-", ":")).EntireRow.Hidden = False
        End If
    End If

     '------------------------------------------------------------------------------------------------------
     ' WEEK 1

    If Target.Address = Range("Week_1_Items_Feb").Address Then
        Range("All_Week").EntireRow.Hidden = True
        If Target.Value = "All" Then
        Range(Range("SELECT_WEEK_FEB").Value).EntireRow.Hidden = False
        Else
        Range(Range("SELECT_WEEK_FEB").Value).Rows(Replace(Target.Value, "-", ":")).EntireRow.Hidden = False
        End If
    End If
End Sub

Showing or Hiding Rows based on Subsequent Criteria

1 Answers