Show Hide ranges with button selection

[ArmySanta]

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


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





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

[Paul_Hossler]

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)

[Zack Barresse]

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.


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

[ArmySanta]

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?

[Aussiebear]

Does that make sense?

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