Forum Discussion
Using multiple ranges
I have an Excel workbook that is used as a form. Depending how the user answers questions or clicks on checkboxes, it is setup to hide or unhide rows. The form is about 250 rows long. It contains multiple scenarios where rows maybe hidden or unhidden. The VBA coding used to do this specifies the specific row(s) to hide or unhide. Example screenshot below. So in the below screenshot, if I were to add a new row at row 11, then I would have to update the VBA coding in lots of places to adjust the rangee by 1 row.
Is there a way to use ranges to where adding or removing row(s) will automatically adjust all the ranges? Below is part of the VBA used in the form.
Select rows 159:162.
Click in the name/address box on the left-hand side of the formula bar.
Type CVVINFO and press Enter.
Select C157.
Click in the name/address box on the left-hand side of the formula bar.
Type CVVINFO_Cell and press Enter.
Select rows 99:105.
Click in the name/address box on the left-hand side of the formula bar.
Type BINBalInfo and press Enter.
Select C97.
Click in the name/address box on the left-hand side of the formula bar.
Type BINBalInfo_Cell and press Enter.
In the code, replace:
159:162 with CVVINFO
C157 with CVVINFO_Cell99:105 with BINBalInfo
C97 with BINBalInfo_Cell
1 Reply
Select rows 159:162.
Click in the name/address box on the left-hand side of the formula bar.
Type CVVINFO and press Enter.
Select C157.
Click in the name/address box on the left-hand side of the formula bar.
Type CVVINFO_Cell and press Enter.
Select rows 99:105.
Click in the name/address box on the left-hand side of the formula bar.
Type BINBalInfo and press Enter.
Select C97.
Click in the name/address box on the left-hand side of the formula bar.
Type BINBalInfo_Cell and press Enter.
In the code, replace:
159:162 with CVVINFO
C157 with CVVINFO_Cell99:105 with BINBalInfo
C97 with BINBalInfo_Cell