Inserting Multiple Columns within an Excel Table - VBA vs Manual

[XOR LX]

Hi all,

Can someone please clarify the following seeming discrepancy and also how I can achieve the code equivalent to the manual process outlined below?

In the attached workbook, in Sheet1 there is an Excel Table. The formulas within this table are not important; they are simply there to allow testing of calculation times.

If I select a single column within this table and then right-click/Insert Columns/Table Columns to the Left, on my machine Excel takes about 7 seconds to perform the operation. I presume that this is the time to recalculate all formulas since inserting a table column is a volatile action.

If I select a range comprising more than one column, e.g. D1:K1, the same manual operation - which now inserts 8 table columns - does not take 56 seconds (8 columns insertions x 7 seconds each), but rather the same time as for a single column insertion, i.e. 7 seconds.

I have no problem with this! What I would like to achieve, however, is the equivalent via VBA. When I use the macro-recorder on the above operation, I get:

Please Login or Register  to view this content.

But when I run this macro it does take 56 seconds, i.e. 7 seconds for each column insertion.

What explains the difference? And how can I achieve the equivalent in VBA of my manual operation which took just 7 seconds?

I know that obviously I can add things to the code like Application.Calculation = xlCalculationManual, etc., but that is not my point - in fact, in Manual Calculation mode the relative performance difference is the same: inserting 8 columns manually = 3 seconds, inserting 8 columns via the above code = 24 seconds.

And amending the above so that it loops over a single line of code is obviously only a cosmetic improvement.

Thanks in advance for any assistance.

Regards

[millz]

How about this?

Please Login or Register  to view this content.

[XOR LX]

Ah, so simple! Thanks a lot, millz!

[XOR LX]

Hi again, millz.

I've run into a small issue. The code you gave works great, apart from when the column insertions are to be done towards the end of the table. Using the workbook I provided, if I want to insert, for example, 4 columns to the left of column BX, if I use

Please Login or Register  to view this content.

I get a "this won't work because it would move cells in a table on your worksheet" error.

What would you propose here? I guess one way would be to first artificially resize the table to the right, run your code, then delete the unwanted columns at the end. Or do you have a better idea?

Thanks again,

Regards

[millz]

I get a "this won't work because it would move cells in a table on your worksheet" error.

That is one weird error, I guess I haven't messed with tables enough to see that error yet.

Apparently this seems to work:

Please Login or Register  to view this content.

Though, I don't know how differently they would react/result in when you have actual data in, or a few columns/rows out of, the table when you do that. But I suppose it will work just fine.

[XOR LX]

Great, the first one seems to work just fine, thanks!

Interesting, this difference between .Range and .DataBodyRange.

I'll test them both on my actual data and see if there's a preference for either.

Cheers

[XOR LX]

Hi millz,

Not sure what's going on here.

See the attached, simpler version, no data this time.

Why, for example, does

Please Login or Register  to view this content.

work, but

Please Login or Register  to view this content.

fail?

I don't know enough about the Table properties you're referencing here (.Range, .Columns) to understand what's going on here.

Thanks again

[XOR LX]

Ok, I think I know what's going on, at least to a certain extent, though I can't yet explain it.

If I add another row, so that the number of rows in the table is now 5, then

Please Login or Register  to view this content.

now works.

So the number of rows in the table must be at least equal to the number of columns I want to insert.

But why? And how can I resolve this, i.e. achieve my desired column insertions even for tables with few rows?

Regards

[XOR LX]

Ahhh...

From the official documentation on Range.Insert:

expression.Insert (Shift, CopyOrigin)

[If the Shift parameter] is omitted, Microsoft Excel decides based on the shape of the range.

So, if I understand correctly, your code wasn't actually inserting table columns, but rather worksheet columns?

Regards

[millz]

Interesting find you have here!

Seems like including the Shift parameter will always make it work?

Please Login or Register  to view this content.

Maybe Excel was trying to find a "matching" origin shape/set of range to copy formats from, but could not determine the direction properly.

Also regarding your question of .Range/.Columns referencing,
.Range would refer to the entire cell range the table is occupying,
.DataBodyRange refers to the same excluding the header row,
and .Columns/.Rows/etc that is after any of the above 2 would refer to the offset from the top-left cell of the table.

You can try moving the table around the sheet (not starting from A1), and see from the result of:

Please Login or Register  to view this content.

[XOR LX]

Thanks for the clarification, I think I'm beginning to understand now.

This point is very interesting:

.Columns/.Rows/etc that is after any of the above 2 would refer to the offset from the top-left cell of the table.

Currently my table begins in column A, though I should account for the possibility of it moving at some point. What would you recommend for the code so that I can continue to refer to the actual worksheet columns for the column insertions?

For example, if, using my last attachment, the table was moved from A1:J5 to C7:L11, and I wanted to insert 5 table columns to the left of column G, using

Please Login or Register  to view this content.

would not make the insertions in the correct place, due to the offset point you make above.

Is there an adjustment I can make to continue to refer to the actual worksheet columns (G:K) to make this insertion work?

Regards

[millz]

so that I can continue to refer to the actual worksheet columns for the column insertions?

If that's all you want, you can simply just reference the worksheet. When you reference the ListObject, normally it would be for anything to deal with that table or its surroundings.

Please Login or Register  to view this content.

[sintek]

Perhaps this will assist in understanding...

Please Login or Register  to view this content.

[rorya]

Is there an adjustment I can make to continue to refer to the actual worksheet columns (G:K) to make this insertion work?

You can use Listobject.range.column to get the first column number, and use that to adjust your insertion point but I'm curious as to why you'd need to insert columns in a table relative to something that isn't part of the table?

[XOR LX]

If that's all you want, you can simply just reference the worksheet. When you reference the ListObject, normally it would be for anything to deal with that table or its surroundings.

Please Login or Register  to view this content.

From the testing I'd done, it seemed that inserting worksheet columns took more processing time than inserting table columns. That's the reason I started down this (tortuous) route.

Regards

[XOR LX]

You can use Listobject.range.column to get the first column number, and use that to adjust your insertion point but I'm curious as to why you'd need to insert columns in a table relative to something that isn't part of the table?

You're completely right - it's illogical and not necessary now that I think about it. I'll restructure it so that the insertions reference the actual table columns, not the worksheet columns.

Thanks a lot