VBA columns delete causing #REF error

[cajsoft]

Hi Experts,
I have a spreadsheet will dynamic columns all with numerical data.. at the very end column there's a Total column that SUM's all the figures.

I wrote a VBA function to delete the columns before the Total column and then Copy and insert figures before the Total column from another Sheet. So weekly this VBA function is ran to update the live figures.

However, when the columns are deleted the total column displays a #REF error. Is there anyway around this progmatically?

 

[Joe4]

Welcome to the Board!

If you delete the column that is being referenced in your formula, that will cause those #REF errors.
However, if you are using VBA already to delete columns, why not add to your existing VBA code to populate the SUM formulas you need AFTER the column deletion?

If you need help with that, please post your current VBA code.
Please see this link on how to post VBA code: How to Post Your VBA Code

 

[cajsoft]

Thanks for your help.. the issue is that the columns are dynamic so I would need to insert a new total column when finished inserting the live data, how would I handle formatting (ie headers, spacing etc) as there is more data below (not shown in image above) that the total column is used for.

 

[Joe4]

You can keep it like it is, and then just look for the "In Plan Total" header (whereever it is at the time), and then re-enter the formula, reflecting the actual columns at that time.

Or you could do it without VBA, if you set up your SUM formula like this: Dynamic sum formula for summing between two dynamic columns | The Excel Factor

 

[cajsoft]

thanks for your help Joe.

much appreciated.

 

[Joe4]

You are welcome.
Out of curiosity, which method did you go with?

 

[cajsoft]

strangely enough, I found that if I just insert a blank column before the data it seemed to work... strange.

 

[Joe4]

strangely enough, I found that if I just insert a blank column before the data it seemed to work... strange.

That makes sense. The reason is because you only will get that error when you specifically delete the column the formula is referencing.
For example, if your formula was:

=SUM(A2:M2)

you would only get that error if you deleted columns specifically mentioned in your formula (the "endpoints"), i.e. "A" or "M".
If you delete any other column, it will not cause that reference error.

So if you include a blank column just before your SUM formula, and use that in your formula and never delete it, your formula won't ever run into that situation.
So you have successfully created a litte "hack" to get around the issue!

 

[cajsoft]

yes.. seems to do the trick... while I'm on the same subject.. is it possible to sort horizontally across the columns..? from the image above we need the columns sorted (left to right) by the first date value (ie 08/03/2023 - 23/03/2023) p.s. I know its sorted in the image above but there is extra columns to the left not captured on the image where dates are jumble up.

 

[Joe4]

yes.. seems to do the trick... while I'm on the same subject.. is it possible to sort horizontally across the columns..? from the image above we need the columns sorted (left to right) by the first date value (ie 08/03/2023 - 23/03/2023) p.s. I know its sorted in the image above but there is extra columns to the left not captured on the image where dates are jumble up.

That is really a brand new and totally different question, and as such, should be posted in a new thread.
However, see if this helps: How to Sort Horizontally in Excel [Columns Sorting]
If it does not solve your issue, please post the question to a new thread.