Making Rows Relative in VBA

I'm using the following code on the worksheet to allow specified rows to be shown/hidden when a specified cell is double clicked. The problem I have is that new rows are constantly being inserted into the sheet which then throws all the cell and row references out.

How can I make these relative references, so that if new rows are inserted anywhere in the sheet, all the references in the code update accordingly to reflect their new position?

Please Login or Register  to view this content.

Many thanks

Use Named_Ranges in place of Rows(Whatever)...

Hi hafizimran,

Okay, I'll give that a go, but how about for the Target Address?

So, I tried it with Named Ranges at it doesn't appear to work.

Is there another solution to this problem that might work?

Though I'm problably doing it wrong. Is anyone able to help. I've attached a simple example. I need to be able to insert rows and for the Target address and row references to be relative so that they still work when news rows are intserted anywhere within the sheet.

I'm still very new to VBA, so my knowledge is a bit lacking.

Many thanks

Here is your file with altered code.

Hi hafizimran,

That works perfectly, many thanks. Is there a way to expand on that to allow the worksheet to be protected, so that when the user double clicks on the specified cell, the sheet unlocks to show the relevant rows and once shown, the sheet locks again and then the same when the user wants to hide the rows by double clicking the cell?

Many thanks

Yes... Add code to unprotect and protect sheet before and after the code like this:

Please Login or Register  to view this content.