Changing all Modules in workbook

[DylanJandB]

Guys,

I have a workbook with alot of different macros within it, It uses all columns and basically I want to add a column into the workbook but when I do this it will disturb the other macros as they will be looking for the wrong columns and cells.

Basically what I'm asking is it possible to add a column into this workbook and the macros automatically change so they don't be disturbed and they change to the correct columns/cells? I'm assuming not but its worth the ask. I assume i have to do this manually.

[Rudi]

Sounds like your macros are using "fixed" cell references.
One can update the macros to rather use relative referencing (.Find() or .Offset(), etc)
It might be a BIG job if you have many macros or the macros are large since a lot of the references will need to be rewritten.

[HansV]

Using code to rewrite code is possible, but I'd avoid it.

It may be better to rewrite the code to get the correct range on the fly. Some examples:

1) You can replace a fixed range such as Range("A1:M500") with Range("A1").CurrentRegion. This is the contiguous range starting in cell A1.

2) If for example row 5 contains column headers, you can get the last used cell in this row as follows:

Dim rngLastCell As Range
Set rngLastCell = Cells(5, Columns.Count).End(xlToLeft)

[DylanJandB]

Yeah i've been using fixed cell references, but I remember reading something here once that someone had a macro or something to change them all to the correct ones after adding a column in. Maybe I'm wrong or just imagining things haha, Thanks anyways!

[HansV]

Having the macros updated automatically as you add (or remove) columns would be difficult; it would cause a lot of overhead and be prone to crashes
A macro that you run manually would be less vulnerable.
But it would be much better to modify the code to use dynamic ranges instead of fixed ranges.

[PJ_in_FL]

I've learned to use named range references for macros to prevent these defects from occurring.

Changing RANGE("A1:M500") to RANGE("MY_DATA") also provides a bit of self-documentation if you've used descriptive names when creating the named ranges.

If the range is defined dynamically, then you also don't need to be concerned if additional rows are added to your data.

To help with all that, look at using the Name Manager add-in by Jan Karel Pieterse.