Refering to column with a "given name" in VBA

[Theo22]

Dear All

First I wish you a happy new year 2019.

My problem:
I made macro which is relating to a thousand of lines and columns and this macro is quiet heavy (But very useful !).
In the following months I will have to add and remove columns without to affect my macro.
My problem is that rewrite all my formulas by changing old column names to new column names is to much time consuming.

My question :
Is it possible to give to a column a "given name" (i.e. "climbing") like a constant?
"Given names" of column would be given in respective cells along the first line of the sheet.

Then in the formula of my macro I could refer to the given name of a specific column (i.e. =SUM("climbing")) instead of initial column names (i.e."'=SUM(D:D)).

Therefore, my macro will not be affected by adding or removing column (Except if I give the same name to different columns)


Thanks for your answer.

Théo

[gjcase]

Yes, you can name cells and ranges in Excel. Highlight your range, then select the address bar (above the worksheet and to the left of the formula bar; it will normally contain the address of the active cell. Overwrite that with your range name.

For instance if you want A1:A100 to be named "Climbing", highlight that range, then select the address bar and enter Climbing into it and hit enter. Now, it you select your formula tab and select the Name Manager, you will see that the named range exists.

From VBA, you can refer to this range as Range("Climbing").

[queuesef]

I may have 2 tidbits of assistance: If you Name your ranges, using TOP Row( Under: Formulas/ Create from Selection /Top Row) it will allow you to use the Named Range for each column. If you use this with making your data a TABLE (Home/ Format as table), you will have the benefits of a Table. Specifically, if you Add rows or Delete rows - your named range will adjust automatically! Words of Wisdom from my late mom: "Take a chance...Columbus did!"
Please add reputation if this helps you.

[Theo22]

Thanks a lot for your answer !

Still have to fully implement it in my project but it seems to solved my problem !

Have a good day,