Automatically update row references in VBA code when rows are updated in Excel?

Question

Hi,

I have some VBA code to hide some rows that looks like this:

If Range("$B$4") .Value = "Test" Then Rows("37:50") .Hidden = True

If Range("$B$4") .Value = "Test" Then Rows("65") .Hidden = True

and so on...

If I add some new rows in the Excel file, however, (e.g. say I add 5 rows in the Excel file after row 20) then the vba code does not hide the correct rows anymore. How can I revise this code to properly hide the desired rows after adding new rows to the Excel file?

Thnx for any help!

Answer 1

Use

If Range("$B$4").Value = "Test" Then  
    Range("RowRange1").EntireRow.Hidden = True  
    Range("RowRange2").EntireRow.Hidden = True  
End If

Answer 2

I'd use named ranges.

Select rows 37:50, click in the name box on the left hand side of the formula bar, enter a name, for example RowRange1, and press Enter.

Select row 65 and similarly name it for example RowRange2.

You can then use

If Range("$B$4") .Value = "Test" Then Range("RowRange1") .Hidden = True

If Range("$B$4") .Value = "Test" Then Range("RowRange2") .Hidden = True

Excel will automatically update the definition of the named ranges as you insert or delete rows above them.

Answer 3

TY. This worked!

Answer 4

Hi. I tried what you suggested on one row to see if it would work.

Selected row 37 in excel and typed RowRange1 in the name box.

Changed vba code to If Range("$B$4") .Value = "Test" Then Range("RowRange1") .Hidden = True

Got error message below:

Answer 5

Thanks! I'll give it a shot!