Hide Rows using a check box dynamically.

[thejarz]

I am writing some documentation in excel. What I want to do is a hide a row (which I understand how to do). But what I am not sure how to do is assign the range of rows to hide dynamically. For example I have CheckBox1 locked to row A6 which hides range A1:A5. But then later I add 5 rows above A1, thereby moving the data in range A1:A5 to A5:A10 (including checkbox.) My problem is I am still left with the VBA hardcoded to A1:A5, so I am not hiding the correct range.

Is there a variable I can use that references the cell value that the checkbox is located in? Or any other ideas of how to do this?

Thanks for any help.

[duncandhu]

I think you could use a named range - the references would change accordingly when you insert/delete rows/columns

Thanks,
Duncan

[Solus Rankin]

Will it always be all the rows above the checkbox?

[thejarz]

I think you could use a named range - the references would change accordingly when you insert/delete rows/columns

Thanks,
Duncan

This resolved the issue. Thanks for the help. I selected the range of rows in the spreadsheet and name them then referenced that name in the VBA code and was able to hide that range.

Thanks!

Example:
Select range, name using the name box (upper left hand corner of spreadsheet). Reference name in the VBA code, in this case "em_rw"

Sub cb_hide_emrw()
'This hides or shows certain cell ranges in the workplan dependent on which
If Sheet8.Shapes("cl_accesscsm").ControlFormat.Value = 1 Then
Sheet8.Range("em_rw").EntireRow.Hidden = True
Else
Sheet8.Range("em_rw").EntireRow.Hidden = False
End If
End Sub