0

In Excel 2007 there was no issue but when I hit the below line in Excel 2016, it now takes over a minute each time. There are only 300 rows in the column. All I want to do is cut a column and paste it next to another column.

Selection.Insert Shift:=xlToRight

Sample code is as follows, but I have 30 odd of these so it is taking half an hour.

Columns("E:E").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight

Any ideas why?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
RShome
  • 489
  • 2
  • 11
  • 35
  • [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) should speed it up. Test if it takes the same/similar amount of time if you do it manually with ctrl+x/ctrl+v. If yes then it is no programming issue, but an issue with your worksheet and the data it contains. – Pᴇʜ Jan 16 '19 at 10:20
  • Are you sure that you only have 300 rows? This is executed in no time if the sheet is small, but takes forever if a cell far down/right is filled (maybe accidently). – FunThomas Jan 16 '19 at 10:28
  • Hi, I have tried this and still an issue on line Selection.Insert Shift:=xlToRight, I even tried using a named range to only select the 300 rows in the column with data and still takes ages – RShome Jan 16 '19 at 10:42
  • Have you tried any other methods, like insert the column, then cut and paste it, but just the data. Also, maybe worth as @FunThomas says, checking what data you have. Maybe try calculation off for this operation to? – Nathan_Sav Jan 16 '19 at 10:42
  • I ask again: How long does it take if you do it manually with ctrl+x/ctrl+v? – Pᴇʜ Jan 16 '19 at 10:57
  • Could you post the whole code. If it is taking forever, you might have the code in an Event and it should be written differently. – VBasic2008 Jan 16 '19 at 10:57
  • Yes it takes the same amount of time copying and pasting manually. – RShome Jan 16 '19 at 11:03
  • Press Control-Shift - End - this will take you to the bottom right of the occupied area of the spread sheet. Is that actually row 300? – Harassed Dad Jan 16 '19 at 11:08
  • Yes it takes the same amount of time copying and pasting manually. Actually I tried just copying one call and pasting and it still took a while. The data in the cell is just 'ID" ! I'm not able to paste full code due to security where I work. – RShome Jan 16 '19 at 11:10
  • Nope definitely only 300 rows of data after pressing CTRL+SHIFT+END – RShome Jan 16 '19 at 11:23
  • • Do you have any events in VBA if so please show their code. • Did you try to copy paste the data into a completely new workbook and test it their if you got the same issues? – Pᴇʜ Jan 16 '19 at 12:18
  • Yes I copied all the sheets and macros to a new workbook and still the same performance issues. The file itself is only 10MB so not a massive workbook by any means. No events in VBA either. I think it is to do with the fact there is fair bit of data in multiple sheets? – RShome Jan 16 '19 at 12:40
  • Well it can can happen if there are many formulas in your sheet especially if they are volatile they can cause a heavy load on re-calculation. Is it slow too if you set calculation to manual before you cut/paste? – Pᴇʜ Jan 16 '19 at 13:09

2 Answers2

2

Do you have formulas and maybe even external references? Then please try this:

ActiveWorkbook.UpdateLinks = xlUpdateLinksNever
ActiveWorkbook.UpdateRemoteReferences = False
Application.Calculation = xlManual

ActiveSheet.Columns("E:E").Cut
ActiveSheet.Columns("C:C").Insert Shift:=xlToRight

ActiveWorkbook.UpdateLinks = xlUpdateLinksAlways
ActiveWorkbook.UpdateRemoteReferences = True
Application.Calculation = xlCalculationAutomatic

' If it's faster, then uncomment following line additionally
' Application.CalculateFull
Asger
  • 3,822
  • 3
  • 12
  • 37
  • Thanks very much but unfortunately this did not work. I don;t have any formulas or external references. If I ever find a solution I will update this post – RShome Jan 17 '19 at 15:07
0

I had to deal with an Excel xlsx file generated by a Payroll system. Not sure what is causing the slowness on column insert. Inserting a column into a 88,000 row file takes about 25 seconds.

I discovered that if I copy the entire worksheet to a new sheet as values and number formats, the insert column step will run almost instantly. The copy entire worksheet portion takes just 3 seconds! There are no formulas in the file nor conditional formatting.

This is the logic I used:

' Copy source worksheet
Dim rng As Range
Set rng = Worksheets("Sheet1").Cells
rng.Copy ' note that wks.Cells.Copy is very slow

' add new worksheet
Dim newWks As Worksheet
Set newWks = Sheets.Add(After:=ActiveSheet)
newWks.Name = "Values Only"

' paste values and number format into new worksheet
newWks.Range("A1").Select
' xlPasteValues is fast too
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
' if I also include a xlPasteFormats then the insert column will become very slow once again
'  Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


' now the column insert is blazingly fast!
newWks.Columns(14).Insert
newWks.Cells(1, 14).Interior.ColorIndex = 35
' etc.
LeslieM
  • 2,105
  • 1
  • 17
  • 8