hide non consecutive rows based on cell values in 2 columns

MarcC

New Member
Joined
May 26, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Dears,

trying to achieve the following:
I have a toggle button that decides whether the condition in column c or in column g should be used, and resets the sheet to fully visible when switched.
I have 12 checkboxes that can be checked / unchecked and only the rows required are visible.
I have the following code per checkbox, which works fine if only that one Checbox is checked. Question is now how to code it when i check 2 or more Checkboxes and want to have the rows visible from all the checked checkboxes.

Private Sub CheckBox1_Click()
CheckBox1.Caption = "APR"
If CheckBox1 = True And ToggleButton1.Value = True Then
For Each cell In ActiveSheet.Range("G10:G250")
If Not cell.Value = "APR" Then
cell.EntireRow.Hidden = True
End If
Next cell
ElseIf CheckBox1 = True And ToggleButton1.Value = False Then
For Each cell In ActiveSheet.Range("D10:D250")
If Not cell.Value = "APR" Then
cell.EntireRow.Hidden = True
End If
Next cell
Else
[10:250].EntireRow.Hidden = False
End If
End Sub

As you can see, i'm new to this :P
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Yes, that is quite an enigma. Especially since there is no indication of what the checkboxes are used for. Athough one could guess that they are associated with months of the year since there are twelve of them. And one could guess that the months are listed in columns D and G. But nothing in the narrative of the OP tells us that. Also, nothing tells us if the checkboxes are namede sequentially from Checkbox1 thru Checkbox12, so one would also have to assume that. But if all the assumptions are true, then you should be able to an If...E;lseIf statement as an algorithm to check each checkbox value and execute a hide/unhide command accordingly.
 

MarcC

New Member
Joined
May 26, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thanks for your fast reply. your asumptions are true. by selecting the month(s) i only want to see the rows that contain that month in column D or G defined by the toggle button.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I did not test this orher than for compile. But in theory, it should give the same results as your original code, but for any number of check boxes. Because of the double loop, it might take several seconds to complete the macro.

VBA Code:
Sub t()
Dim ary As Variant, c As Range, rng As Range, i As Long
ary = Array(Chedkbox1, CheckBox2, Checkbox3, Checkbox4, Checkbox5, Checkbox6, Checkbox7, Checkbox8, Checkbox9, Checkbox10, Checkbox11, Checkbox12)
rng = Union(Range("D10:D230"), Range("G10, G230"))
rng.EntireRow.Hidden = True 'Hide all rows in rng
    For Each c In rng
        For i = LBound(ary) To UBound(ary)
            If ary(i).Value = True And ToggleButton1 = True Then
                If c.Value = ary(i).Caption Then
                    c.EntireRow.Hidden = False  'Unhide matched rows
                    Exit For
                End If
            End If
        Next
    Next
End Sub
 

MarcC

New Member
Joined
May 26, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
still added the Set when assigning the range, and it runs nicely :) really appriciate your help on this. Thanks !
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
still added the Set when assigning the range, and it runs nicely :) really appriciate your help on this. Thanks !
good catch, regtards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,130,245
Messages
5,641,070
Members
417,192
Latest member
choomkey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top