Keeping macro cell references when adding/removing rows/columns

[dlukas]

Is there a way to use cell references in macros so that the cell references are automatically adjusted if row/columns are added/removed (the way that formulas in a worksheet automatically adjust)? As far as I can tell, I would either have to name every range that is used in the macro, or go into the VBA and manually adjust all affected cell references. Thanks.

[Leith Ross]

Hello dlukas,

Welcome to the Forum!

Your question is to general to answer. You should either post an example of your code or what you want the code to do (before and after). Range objects are the backbone of Excel and there are many ways to use them. Without knowing what you want to accomplish, posting a solution becomes a guessing a game. The more detail you can provide, the faster you will receive an answer.

[dlukas]

I haven't written the code yet, but essentially this is what it will do (the inputs for the macro will be hardcoded in a range of cells in a financial model):

1. copy the first value in the range
2. paste the value as one of the model's inputs
3. copy a specific output from the model
4. paste it into a separate output table created by the macro
5. loop back and copy the second hardcoded item, repeat steps 2-5

Ultimately the table that the macro creates will be a sensitivity analysis, showing how the selected output changes as the specified input changes. Once I get it working I will expand the functionality to perform multiple sensitivity analyses between any variables chosen... however the problem is that the model itself may have to be changed concurrently. If that happens, it will throw off all the cell refereces for the model's inputs and outputs. If there's a way to avoid this so that the cell references in the macro "slide" along with row/column changes in the model it would save a LOT of time.

Hopefully that gives enough background... thanks all!

[foxguy]

Is there a way to use cell references in macros so that the cell references are automatically adjusted if row/columns are added/removed (the way that formulas in a worksheet automatically adjust)? As far as I can tell, I would either have to name every range that is used in the macro, or go into the VBA and manually adjust all affected cell references. Thanks.

I believe you are right. You have to name the ranges or manually adjust all references.

If there is another solution, please bump this thread to the top so I can see the solution.

[dlukas]

That's what I'm beginning to suspect. For this very specific situation only, I think I can eliminate some of the work that the macro would need to do by using a combination of data tables that reference INDIRECT formulas that reference INDEX formulas... the fundamental problem of non-sliding cell references remains but at least now I have reduced the number of ranges I have to name in order to address the problem.