input on hiding rows by values in a control column

[Niss1]

I am improving on som old worksheets and I need some help hiding rows.

My excel sheet has activex checkboxes that determines whether or not a row is hidden

old code

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
    rows("29:53").Hidden = False
Else
    CheckBox1.Value = False
    rows("29:53").Hidden = True
End If
End Sub

The problem is that i have to hardcode in which rows are to be hidden, and i would like to have a bit more userfriendly approach as i would like to use this across several worksheets without having to manually change the numbering every time i change something. The problem is that the checkboxes hide different numbers of rows.

So i have added in an control column (AJ), counting upwards for every new "block" of rows that i want to hide(easy to fix/keep updated)

I would like checkbox1 if unchecked, to hide rows from number 1 in column AJ to where it encounters the number 2 in column AJ. (Lookup 1 and 2; hide rows from row with 1 to row with 2(-1)

Checkbox2 if unchecked hiding rows from where it finds number 2 to where it finds number 3. (Lookup 2 and 3;hide rows from row with 2 to row with 3(-1)

I have tried to this, but i get a "type mismatch" error

Private Sub CheckBox1_Click()
Dim lRowa, lRowb As Long
    lRowa = Application.WorksheetFunction.Match(1, Range("AJ1:AJ1000"), 0)
    lRowb = Application.WorksheetFunction.Match(2, Range("AJ1:AJ1000"), 0)
    lRowb = lRowb - 1
If CheckBox1.Value = True Then
    rows("lRowa:lRowb").Hidden = False
Else
    CheckBox1.Value = False
    rows("lRowa:lRowb").Hidden = True
End If
End Sub

I hope you can help me a bit with this!

 

[moonfish]

I think you need to replace

lRowa:lRowb

with

lRowa&":"&lRowb

You can't put variables inside quotation marks.

 

[Niss1]

I think you need to replace

lRowa:lRowb

with

lRowa&":"&lRowb

You can't put variables inside quotation marks.

hmm, that didnt quite work either, but yeah, the quotation marks are probably the problem

 

[Niss1]

I got it working. had to add a space before the &'s

like this

lRowa &":" &lRowb

thanks again!