Solved: VBE column references

[jmenche]

Howdy,

This is more of a rant than a request but I'll run this up the flagpole anyway. I am writing a routine that populates a worksheet. Every time I add or delete a column to the worksheet, I have to go into my routine and change allllllllllllllllll of the column references by hand :banghead: .

Is there anyway to automate this??? Too bad the VBE doesn't update like a formula does ;-)

Thanks for listening!

[rory]

You would either need some sort of headers in the worksheet that you can use to locate the correct column, or you should use constants in your code so that you only have to change the column number in one location.

[mikerickson]

Used named ranges for the column references. They will adjust to insert/delete.


For i = 1 to 256
Cells(1,i).EntireColumn.Name="Column" & CStr(i)
Application.Names("Column" & CStr(i)).Visible = False
Next i


That will work if the problem columns are added by the user while using the spreadsheet).

If these changes are part of the development of the spreadsheet, rather than the designed use of the spreadsheet; rant accepted, I feel your pain.

[geekgirlau]

I tend to use a heading row wherever possible and use MATCH to identify the column number ... don't know if this is appropriate in your situation

[jmenche]

Thanks all. I'll try some of those ideas out.

:friends: