How to update VBA cell references when deleting columns?

[stubarny]

Hello,

I've noticed that when I delete columns from my worksheet the cell references in my VBA code don't update, so the macros stop working.

Please could someone tell me if there is a way of updating these references when I delete columns, or tell me if there is a workaround?

Thanks,

Stu

 

[JackDanIce]

You can but depends on how you code your macro, can you post it please?

 

[Jon von der Heyden]

Perhaps you should be using defined names for your ranges and then refer to the names in your code.

For instance, say Column X in Sheet1 is 'Amount'. You could set-up a name 'Amount', referring to Sheet1!$X:$X, in name manager. Then in your code you refer to the name:

Sheets("Sheet1").Range("Amount")

This way if you delete any column between A:W, Amount will move relatively to column W and your macro will continue to work.

 

[stubarny]

Ahh thank you