Show Hide ranges with button selection

Hi, I need help with VBA. Please indulge me.

My buttons called crse1, crse2, crse3, crse4 and crse5 are supposed to call their respective cases in the CallHideAndRevealCells() sub. In each of the cases, I set the value of the Range and pass that range into their respective functions depending on the amount of mods it is has. I tried asking chatGPT but its not working.

My intent is for the cells to reveal themselves everytime I click the button and immediately go through the loop and hide all but the first line with an "n" in the cells.

Please help.

Cheers,
ArmySanta

Code:

---

Sub CallHideAndRevealCells()    ' Get the name of the button that was clicked
    Dim buttonName As String
    Dim rng As Range
    buttonName = Application.Caller
   
    ' Call the appropriate function based on the button name
    Select Case buttonName


        Case "crse1"
            Set rng = Range("C2:C21")
            Hide_And_Reveal_Courses_1_Mod rng

        Case "crse2"
            Set rng = Range("C49:C88")
            Hide_And_Reveal_Courses_2_Mods rng

        Case "crse3"
            Set rng = Range("C194:C253")
            Hide_And_Reveal_Courses_3_Mods rng

        Case "crse4"
            Set rng = Range("C666:C745")
            Hide_And_Reveal_Courses_4_Mods rng


        Case "crse5"
            Set rng = Range("C768:C867")
            Hide_And_Reveal_Courses_5_Mods rng

    End Select
End Sub

---

Code:

---

Sub Hide_And_Reveal_Courses_1_Mod(rng As Range)

    Dim ModOne1 As Range
    Dim ModOne2 As Range
    Dim ModOneRange As Range
   
    Set ModOneRange = rng
   
    rng.EntireRow.Hidden = False


    'Loop through the cells in the module
    For Each ModOne1 In ModOneRange.Cells
        'If the cell is "n", hide the next set of the course
        If ModOne1.Value = "n" Then
            For Each ModOne2 In Range(ModOne1.Offset(2, 0), ModOneRange.Cells(ModOneRange.Cells.Count)).Cells
                If ModOne2.Value = "n" Then ModOne2.EntireRow.Hidden = True
            Next ModOne2
        End If
    Next ModOne1
End Sub








Sub Hide_And_Reveal_Courses_2_Mods(rng As Range)
   
    ' Declare variables to hold the different ranges we'll be working with
    Dim ModOne1 As Range
    Dim ModOne2 As Range
    Dim ModTwo1 As Range
    Dim ModTwo2 As Range
    Dim ModOneRange As Range
    Dim ModTwoRange As Range
   
    ' Set ModOneRange to the range passed into the sub
    Set ModOneRange = rng
   
    ' Set ModTwoRange to be the range directly below ModOneRange
    Set ModTwoRange = ModOneRange.Offset(1, 0)


    ' Unhide all rows in the range passed into the sub
    rng.EntireRow.Hidden = False


    ' Loop through the cells in the first mod (ModOneRange)
    For Each ModOne1 In ModOneRange.Cells
       
        ' If the value of the cell is "n", we need to hide the next set of rows
        If ModOne1.Value = "n" Then
           
            ' Loop through the cells in the range starting from the cell two rows below ModOne1
            ' and going until the end of ModOneRange (the last row in the range passed into the sub)
            For Each ModOne2 In Range(ModOne1.Offset(2, 0), ModOneRange.Cells(ModOneRange.Rows.Count, 1)).Cells
               
                ' If the value of the cell is "n", hide the entire row
                If ModOne2.Value = "n" Then ModOne2.EntireRow.Hidden = True
           
            Next ModOne2
           
        End If
       
    Next ModOne1
   
    ' Loop through the cells in the second mod (ModTwoRange)
    For Each ModTwo1 In ModTwoRange.Cells
       
        ' If the value of the cell is "n", we need to hide the next set of rows
        If ModTwo1.Value = "n" Then
           
            ' Loop through the cells in the range starting from the cell two rows below ModTwo1
            ' and going until the last row in ModTwoRange
            For Each ModTwo2 In Range(ModTwo1.Offset(2, 0), ModTwoRange.Cells(ModTwoRange.Rows.Count, 1)).Cells
               
                ' If the value of the cell is "n", hide the entire row
                If ModTwo2.Value = "n" Then ModTwo2.EntireRow.Hidden = True
           
            Next ModTwo2
           
        End If
       
    Next ModTwo1
   
End Sub


Sub Hide_And_Reveal_Courses_3_Mods(rng As Range)


    Dim ModOne1 As Range
    Dim ModOne2 As Range
    Dim ModTwo1 As Range
    Dim ModTwo2 As Range
    Dim ModThree1 As Range
    Dim ModThree2 As Range
   
    Dim ModOneRange As Range
    Dim ModTwoRange As Range
    Dim ModThreeRange As Range
   
    Set ModOneRange = rng
    Set ModTwoRange = ModOneRange.Offset(1, 0)
    Set ModThreeRange = ModOneRange.Offset(2, 0)


    rng.EntireRow.Hidden = False


    'Loop through the cells in the first module
    For Each ModOne1 In ModOneRange.Cells
        'If the cell is "n", hide the next set of the course
        If ModOne1.Value = "n" Then
            For Each ModOne2 In Range(ModOne1.Offset(2, 0), ModOneRange.Cells.Count).Cells
                If ModOne2.Value = "n" Then ModOne2.EntireRow.Hidden = True
            Next ModOne2
        End If
    Next ModOne1
   
    'Loop through the cells in the second module
    For Each ModTwo1 In ModTwoRange.Cells
        'If the cell is "n", hide the next set of the course
        If ModTwo1.Value = "n" Then
            For Each ModTwo2 In Range(ModTwo1.Offset(2, 0), ModTwoRange.Cells.Count).Cells
                If ModTwo2.Value = "n" Then ModTwo2.EntireRow.Hidden = True
            Next ModTwo2
        End If
    Next ModTwo1
   
    'Loop through the cells in the third module
    For Each ModThree1 In ModThreeRange.Cells
        'If the cell is "n", hide the next set of the course
        If ModThree1.Value = "n" Then
            For Each ModThree2 In Range(ModThree1.Offset(2, 0), ModThreeRange.Cells.Count).Cells
                If ModThree2.Value = "n" Then ModThree2.EntireRow.Hidden = True
            Next ModThree2
        End If
    Next ModThree1
End Sub




Sub Hide_And_Reveal_Courses_4_Mods(rng As Range)


    Dim ModOne1 As Range
    Dim ModOne2 As Range
    Dim ModTwo1 As Range
    Dim ModTwo2 As Range
    Dim ModThree1 As Range
    Dim ModThree2 As Range
    Dim ModFour1 As Range
    Dim ModFour2 As Range


    Dim ModOneRange As Range
    Dim ModTwoRange As Range
    Dim ModThreeRange As Range
    Dim ModFourRange As Range
   
    Set ModOneRange = rng
    Set ModTwoRange = ModOneRange.Offset(1, 0)
    Set ModThreeRange = ModOneRange.Offset(2, 0)
    Set ModFourRange = ModOneRange.Offset(3, 0)


    rng.EntireRow.Hidden = False


    'Loop through the cells in the first mod
    For Each ModOne1 In ModOneRange.Cells
        'If the cell is "n", hide the next set of the course
        If ModOne1.Value = "n" Then
            For Each ModOne2 In Range(ModOne1.Offset(2, 0), ModOneRange.Cells(ModOne1.Cells.Count)).Cells
                If ModOne2.Value = "n" Then ModOne2.EntireRow.Hidden = True
            Next ModOne2
        End If
    Next ModOne1
   
    'Loop through the cells in the second mod
    For Each ModTwo1 In ModTwoRange.Cells
        'If the cell is "n", hide the next set of the course
        If ModTwo1.Value = "n" Then
            For Each ModTwo2 In Range(ModTwo1.Offset(2, 0), ModTwoRange.Cells(ModTwo1.Cells.Count)).Cells
                If ModTwo2.Value = "n" Then ModTwo2.EntireRow.Hidden = True
            Next ModTwo2
        End If
    Next ModTwo1
   
    'Loop through the cells in the third mod
    For Each ModThree1 In ModThreeRange.Cells
        'If the cell is "n", hide the next set of the course
        If ModThree1.Value = "n" Then
            For Each ModThree2 In Range(ModThree1.Offset(2, 0), ModThreeRange.Cells(ModThree1.Cells.Count)).Cells
                If ModThree2.Value = "n" Then ModThree2.EntireRow.Hidden = True
            Next ModThree2
        End If
    Next ModThree1
   
    'Loop through the cells in the fourth mod
    For Each ModFour1 In ModFourRange.Cells
        'If the cell is "n", hide the next set of the course
        If ModFour1.Value = "n" Then
            For Each ModFour2 In Range(ModFour1.Offset(2, 0), ModFourRange.Cells(ModFour1.Cells.Count)).Cells
                If ModFour2.Value = "n" Then ModFour2.EntireRow.Hidden = True
            Next ModFour2
        End If
    Next ModFour1


End Sub


Sub Hide_And_Reveal_Courses_5_Mods(rng As Range)


    Dim ModOne1 As Range
    Dim ModOne2 As Range
    Dim ModTwo1 As Range
    Dim ModTwo2 As Range
    Dim ModThree1 As Range
    Dim ModThree2 As Range
    Dim ModFour1 As Range
    Dim ModFour2 As Range
    Dim ModFive1 As Range
    Dim ModFive2 As Range


    Dim ModOneRange As Range
    Dim ModTwoRange As Range
    Dim ModThreeRange As Range
    Dim ModFourRange As Range
    Dim ModFiveRange As Range
   
    Set ModOneRange = rng
    Set ModTwoRange = ModOneRange.Offset(1, 0)
    Set ModThreeRange = ModTwoRange.Offset(1, 0)
    Set ModFourRange = ModThreeRange.Offset(1, 0)
    Set ModFiveRange = ModFourRange.Offset(1, 0)


    rng.EntireRow.Hidden = False


    'Loop through the cells in the first mod
    For Each ModOne1 In ModOneRange.Cells
        'If the cell is "n", hide the next set of the course
        If ModOne1.Value = "n" Then
            For Each ModOne2 In Range(ModOne1.Offset(2, 0), ModOneRange.Cells(ModOne1.Cells.Count)).Cells
                If ModOne2.Value = "n" Then ModOne2.EntireRow.Hidden = True
            Next ModOne2
        End If
    Next ModOne1
   
    'Loop through the cells in the second mod
    For Each ModTwo1 In ModTwoRange.Cells
        'If the cell is "n", hide the next set of the course
        If ModTwo1.Value = "n" Then
            For Each ModTwo2 In Range(ModTwo1.Offset(2, 0), ModTwoRange.Cells(ModTwo1.Cells.Count)).Cells
                If ModTwo2.Value = "n" Then ModTwo2.EntireRow.Hidden = True
            Next ModTwo2
        End If
    Next ModTwo1
   
    'Loop through the cells in the third mod
    For Each ModThree1 In ModThreeRange.Cells
        'If the cell is "n", hide the next set of the course
        If ModThree1.Value = "n" Then
            For Each ModThree2 In Range(ModThree1.Offset(2, 0), ModThreeRange.Cells(ModThree1.Cells.Count)).Cells
                If ModThree2.Value = "n" Then ModThree2.EntireRow.Hidden = True
            Next ModThree2
        End If
    Next ModThree1
   
    'Loop through the cells in the fourth mod
    For Each ModFour1 In ModFourRange.Cells
        'If the cell is "n", hide the next set of the course
        If ModFour1.Value = "n" Then
            For Each ModFour2 In Range(ModFour1.Offset(2, 0), ModFourRange.Cells(ModFour1.Cells.Count)).Cells
                If ModFour2.Value = "n" Then ModFour2.EntireRow.Hidden = True
            Next ModFour2
        End If
    Next ModFour1
   
    'Loop through the cells in the fifth mod
    For Each ModFive1 In ModFiveRange.Cells
    'If the cell is "n", hide the next set of the course
    If ModFive1.Value = "n" Then
        For Each ModFive2 In Range(ModFive1.Offset(2, 0), ModFiveRange.Cells(ModFive1.Cells.Count)).Cells
            If ModFive2.Value = "n" Then ModFive2.EntireRow.Hidden = True
        Next ModFive2
    End If
Next ModFive1
End Sub

---

1. A more descriptive title will usually get more members to take a look, after all most of us are already here to "Please Help"

2. That's a LOT of code and seems to be dependent on worksheet data, so it'd be much easier if you attached a workbook with the macros and enough data to show the issue. Alternative, you can just make a small workbook with only the specific issue (i.e. not the entire project)

So you're just trying to hide all the cells in those specified range, upon its relative button click, which contain a value of "n"? If so, you can just use something like this.

Code:

---

Sub CallHideAndRevealCells()

    Dim CourseRange1 As Range
    Dim CourseRange2 As Range
    Dim CourseRange3 As Range
    Dim CourseRange4 As Range
    Dim CourseRange5 As Range
    Dim CourseButtonName As String
   
    CourseButtonName = Application.Caller
    Set CourseRange1 = ActiveSheet.Range("C2:C21")
    Set CourseRange2 = ActiveSheet.Range("C49:C88")
    Set CourseRange3 = ActiveSheet.Range("C194:C253")
    Set CourseRange4 = ActiveSheet.Range("C666:C745")
    Set CourseRange5 = ActiveSheet.Range("C768:C867")
   
    Select Case CourseButtonName
    Case "crse1": ShowCourses CourseRange1
    Case "crse2": ShowCourses CourseRange2
    Case "crse3": ShowCourses CourseRange3
    Case "crse4": ShowCourses CourseRange4
    Case "crse5": ShowCourses CourseRange5
    End Select
   
End Sub


Sub ShowCourses( _
    ByVal CourseRange As Range, _
    Optional ByVal MatchingValue As String = "n" _
    )
   
    Dim Cell As Range
    Dim FoundCells As Range
   
    CourseRange.EntireRow.Hidden = False
   
    For Each Cell In CourseRange
        If Cell.Value = MatchingValue Then
            If FoundCells Is Nothing Then
                Set FoundCells = Cell
            Else
                Set FoundCells = Union(FoundCells, Cell)
            End If
        End If
    Next Cell
   
    If Not FoundCells Is Nothing Then
        FoundCells.EntireRow.Hidden = True
    End If
   
End Sub

---

Ok, I summarized the code because it is work related. That's why there is enormous gaps in the cell ranges.

Ok so what I am trying to do is to hide all but 1 of the cells that has an "n".
For the mod-type courses, I am trying to hide all but 1 of each the mods of the cells with an "n"
If the user changes the value from an "n" to a "y" and clicks the button, then I want it to show the next value of "n".

Does that make sense?

Quote:

---

Originally Posted by ArmySanta

Does that make sense?

---

Which is essentially what I was alluding to in your previous thread.