What Is Best Practice To Facilitate Column Layout Change In A Project?

[ozex]

This is a general 'best practice at project development stage' question re making any wsheet column layout changes easier.

Issue:
In development, if a worksheet layout change is required or upon project finalsation I have to backtrack on existing references to rows and columns which can easily lead to errors not all of which are immediately apparent if I miss one.
How can a worksheet layout change (as they apply to columns) be accommodated easier?
Can named ranges be applied to columns only and if so how would I do that?

Scope of current project:
Fixed 'display' columns, A to F and a number of hidden 'helper' columns G to R.
A dynamic number of data blocks each separated by an empty row.
Each block has a dynamic number of rows.
6,569 lines of code.
89 subs or functions.

Illustration:
(During project development column headers were different)
Col G = data block row index
Col H = production date
Col I = distrubutor name
Col J = color number of .Cells(x, "E"), (my preferred reference method) or .Range("E" & x)
Col K = data block sort index
Cols M to R = rowsource table constructed for a ListBox control

I am content with dynamic row control and change but column control and change can at times be frustrating to capture all that are needed.

If my explaination is sufficient, what is considered best practice to avoid possible code change errors?

Thank you.

 

[RoryA]

I'd either use tables, or use named ranges. The named ranges only need to be a reference to one cell just to get a column number really.

 

[ozex]

I'd either use tables, or use named ranges. The named ranges only need to be a reference to one cell just to get a column number really.

Thanks Rory, I suspected as such, in particular the tip about a one cell named range is useful. Before writing code I usually mock up some layouts but change is almost inevitable. As I'm sure you will be aware, sometimes design change is easy, other times less so despite find and replace,,,,,,, And for me it also depends on how long ago the project was first written.