Super slow Insert Columns with 40,000 lines of records

Hi,

I am experiencing super slow and some time crashing insert columns with 40,000 lines of records.
I am just wondering, is there better way around this?

Here's the code that's giving me issue.

Columns("E:E").Insert Shift:=xlToRight
...
Columns("P:R").Insert Shift:=xlToRight

I have tried using Range("E1:E40000").Insert... but still no luck of speeding things up or avoiding crashing Excel 2010.

Any suggestion?


Thank you very much

Originally Posted by JBeaucaire

Typically, if anything CAN be done, it will be in reducing the column-calcs associated with the sheet. Suggestions:

1) Turn off Automatic Calculation in your workbook prior to the insert so the formulas won't try to recalc on their own

2) Remove all the Conditional formatting you can from your workbook.

3) With the Conditional Formatting that is left, consider removing it, then reapplying new rules to the sheet ensuring all the cells are highlighted as you recreate each new CF formula, this ensures only one rule is created for the entire range. Again, the less of this, the better, when performance is the goal.

4) Highlight all the empty cells below your data range and do a CLEAR ALL. (ALT-e-a-a)
(do the same with all empty cells to the right of your data)

Hi JBeaucaire,

Thank you for the suggestion.
However, my source data does not have any formula or conditional formatting in them at all.
They are just value data, some with date format.

I have tried the Clear All method. It's a bit faster but still took some time to completed.

Here's the code that's in between the two inserts.

    Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E1").Value = "Title 1"
    
    Columns("P:R").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("I1").Value = "Title 2"
    Range("J1").Value = "Title 3"
    Range("K1").Value = "Title 4"
    Range("L1").Value = "Title 5"
    Range("M1").Value = "Title 6"
    Range("N1").Value = "Title 7"
    Range("O1").Value = "Title 8"
    Range("P1").Value = "Title 9"
    Range("Q1").Value = "Title 10"
    Range("R1").Value = "Title 11"

Please let me know if there's anything other than suggested that would speed up the process.


Thank you very much