Using Multiple Checkboxes to Hide Different Rows Not in a Range

[cursedllama]

Hi All,

I've been scouring this board for a few hours trying to find VBA code that I can apply to my sheet, but nothing has been able to get to the root of my issue yet.

I am trying to make a checkbox that, when checked, unhides rows 8-10, 17-19, 26-28, 35-37... down the line, all the way to 242-244.

I am also trying to make a separate checkbox that, when checked, unhides rows 11-13, 20-22, 29-31, etc... down the line all the way to 245-247. Here's what I have so far, I abbreviated the code since it goes all the way to 246 and it's on my work computer so I didn't want to type it all out (personal PC):

Private Sub CheckBox1_Click()

Range ("8:10,17:19,26:28,35:37,44:46").EntireRow.Hidden = Not Range("8:10,17:19,26:28,35:37,44:46").EntireRow.Hidden

End Sub

and

Private Sub CheckBox2_Click()
Range ("11:13,20:22,29:31,38:40,47:49").EntireRow.Hidden = Not Range ("11:13,20:22,29:31,38:40,47:49")

My issues with this code is obviously that's really long when I go all the way out to 247, it's not very efficient, and that the first checkbox seems to interfere with the hiding of the second one. I'm not sure if it's because I have some merged cells, but depending on checkbox #1, checkbox #2 might not respond. On top of that, and this is a more minor issue, when the boxes are checked, the rows are hidden which is the opposite of what I'd like.

Can you let me know a better way to run this code? I'm having a ton of trouble with both the rows not being in a range, and the interaction between 2+ checkboxes.

Thank you!

 

[cursedllama]

Hi All,

I've been scouring this board for a few hours trying to find VBA code that I can apply to my sheet, but nothing has been able to get to the root of my issue yet.

I am trying to make a checkbox that, when checked, unhides rows 8-10, 17-19, 26-28, 35-37... down the line, all the way to 242-244.

I am also trying to make a separate checkbox that, when checked, unhides rows 11-13, 20-22, 29-31, etc... down the line all the way to 245-247. Here's what I have so far, I abbreviated the code since it goes all the way to 246 and it's on my work computer so I didn't want to type it all out (personal PC):

Private Sub CheckBox1_Click()

Range ("8:10,17:19,26:28,35:37,44:46").EntireRow.Hidden = Not Range("8:10,17:19,26:28,35:37,44:46").EntireRow.Hidden

End Sub

and

Private Sub CheckBox2_Click()
Range ("11:13,20:22,29:31,38:40,47:49").EntireRow.Hidden = Not Range ("11:13,20:22,29:31,38:40,47:49")

My issues with this code is obviously that's really long when I go all the way out to 247, it's not very efficient, and that the first checkbox seems to interfere with the hiding of the second one. I'm not sure if it's because I have some merged cells, but depending on checkbox #1, checkbox #2 might not respond. On top of that, and this is a more minor issue, when the boxes are checked, the rows are hidden which is the opposite of what I'd like.

Can you let me know a better way to run this code? I'm having a ton of trouble with both the rows not being in a range, and the interaction between 2+ checkboxes.

Thank you!

Whoops sorry, I cut my #2 checkbox code off early, but it also has the .EntireRow.Hidden ending and the End Sub, that's not my issue.

 

[NoSparks]

Merged cells are the work of the devil and should be avoided like the plague.

Without merged cells

Private Sub CheckBox1_Click()
    Dim i As Long
Application.ScreenUpdating = False
    For i = 8 To 244 Step 9
        Rows(i).Resize(3).Hidden = Not CheckBox1
    Next i
Application.ScreenUpdating = True
End Sub

Private Sub CheckBox2_Click()
    Dim i As Long
Application.ScreenUpdating = False
    For i = 11 To 247 Step 9
        Rows(i).Resize(3).Hidden = Not CheckBox2
    Next i
Application.ScreenUpdating = True
End Sub