effect adding column worksheet

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Effect of adding a column on a worksheet to all column refs in V

Effect of adding a column on a worksheet to all column refs in V

resolvedResolved · High Priority · Version 2016

Diane has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course

Effect of adding a column on a worksheet to all column refs in V

I was about to add a column to my worksheet when it occurred to me any referencing in vb to column numbers e.g. in my arrays; in my offsets etc will now be incorrect. I could of course add the column on the right, but maybe it makes more logical sense for the column to be adjacent to another mid range. I'm thinking this must be quite a common requirement, what would be the recommended way to write vb to cope with this?

RE: Effect of adding a column on a worksheet to all column refs

Hi Diane

You're quite right it is a common problem when amending spreadsheets with macro's attached.

Best practice emphases the importance of a change log, to keep track of changes and the impact they will have. This will help you quickly identify areas and take the appropriate steps when coding.

Additionally,identify something other than the column letter which will make it easier for Excel to find e.g. the Heading of the table "Employee Name" or "Invoice Amount" this way if the heading is moved Excel will still find it and move the relevant section.

Kind regards

Dennis

RE: Effect of adding a column on a worksheet to all column refs

Thanks Dennis, I will change my references to column name wherever it is possible. There is no way similar around this for arrays, is there?.

Di

RE: Effect of adding a column on a worksheet to all column refs

Hi Diane

The principle applies for arrays too. You could consider using dynamic named ranges to name the range on the spreadsheet and then assign the range to an array in VBA.

Link to a tutorial on dynamic named ranges https://www.excel-easy.com/examples/dynamic-named-range.html

Regards

Dennis

Tue 14 Apr 2020: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Using Excel's MODE function

Use Excel's MODE function to display the most common value present in a particular range of cells.

The Mode function looks like this:

=MODE(cell range)

As an example, if 35 is the most commonly recurring number in a particular cell range, then the function will display 35.

View all Excel hints and tips


Server loaded in 0.11 secs.