I just started with VBA and running into an issue.
In my VBA code I'm using ranges to change for example a value of a cell.
For example: Range("A5").Value
But if a user adds a row above row A5, A5 should automatically change to A5.

Is this possible?

Searched the web (and asked ChatGPT), but haven't found a solution

I just started with VBA and running into an issue.
In my VBA code I'm using ranges to change for example a value of a cell.
For example: Range("A5").Value
But if a user adds a row above row A5, A5 should automatically change to A5.

Is this possible?

Searched the web (and asked ChatGPT), but haven't found a solution

• excel
• vba

Share Improve this question Follow asked Mar 10 at 11:11 Mear1628Mear1628 1555 bronze badges 5

• Did you mean A5 should automatically change to A6? You have to dynamically determine the range you want to change according to some criteria - we don't know why you are changing A5. edit your question to give more detail - including a snippet of your code – CHill60 Commented Mar 10 at 11:16
• How do you know that a user added a row above resp. how do you know that you should not change the value in A5? BTW, you probably mean A5 should automatically change to A6. – Storax Commented Mar 10 at 11:17
• Your requirement is at least unusual. Could you describe in more detail the reason why you need this functionality? Maybe add a screenshot of your sheet and the code you are trying to make work to your post. There may be different approaches. – VBasic2008 Commented Mar 10 at 12:18
• 2 The best way to help us help you here is to explain exactly how you would identify the correct range if you (not your VBA) were looking at the sheet after the user has made their edits. What "landmarks" would you use to find the cell to be changed? – Tim Williams Commented Mar 10 at 15:57
• My guess is that you are using that value later on your code, perhaps define a keyword and look for it instead of relying in ranges? As in example Temperature (a4) and its value in B4 25, so you look for the word temperature and offset 0,1 – Sgdva Commented Mar 10 at 17:45

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

You could create a named range that refers to a specific cell, and this named range will automatically adjust when rows are inserted or deleted.

To create a named range, go to the Formulas tab in Excel, click on "Define Name," and enter a name for your range, say "DynamicCell". You can then reference this named range in your VBA code.

This approach will fail in case somebody deletes the row with the named range.

Range("DynamicCell").Value = "Your Value"