VBA Checkbox Question

[Jer2224]

Morning all,

I've been looking around to a solution to this for a while, and have not gotten past a particular point - looking for some expert advice.

I have a spreadsheet with active data in rows 12 to past 600+, which tracks a particular process. The end result of the process is essentially a 'y' in column BS for each row.

What I want to do is to put a checkbox somewhere in the header rows (rows 1 to 11) that when checked hides all the rows in range 12 to 600 that have 'y' in column BS.

Now, being new to VBA, I've gotten this far, by attaching this to an ActiveX checkbox called 'CheckBox1':

Private Sub CheckBox1_Change()
    Range("12:20,40:60").Rows.Hidden = CheckBox1.Value
End Sub

...in that this will hide manually specified rows 12 to 20 and 40 to 60, but what I want to do is replace these manually applied ranges with an 'IF' statement that queries cell BS in each row.

Infuriatingly, it doesn't want to play and gives a runtime error 1004 - Method 'Range' of object '_Worksheet' failed.

Any thoughts on how I might get this to work? Happy to clarify if I've not explained it very well.....

Cheers in advance.
Jeremy.

 

[GTO]

Try:

Option Explicit
    
Private Sub CheckBox1_Click()
Dim Cell As Range
    
    If CheckBox1 Then
        For Each Cell In Range("BS12:BS600")
            If LCase(Cell.Value) = "y" Then
                Cell.Rows.Hidden = True
            End If
        Next
    Else
        Range("BS12:BS600").EntireRow.Hidden = False
    End If
End Sub

 

[Jer2224]

Genius! Works like a dream....

Many thanks for your help.
Cheers.
Jeremy.

 

[GTO]

Cheers back and of course you are most welcome