Move column in Excel 2007, extremely slow HELP!

[Laavista]

I'm using Excel 2007 (operating system is Windows 7). Since changing my VBA program from Excel 2003 to Excel 2007, moving a column is extremely slow. I have a program that has to move 5 columns in a specific order, and it's taking 10+ minutes. I have 4000 rows of data

I originally selected the entire column, cut it, then inserted the cut cells.
I thought if I would select the specific rows, then cut it and insert just those rows that it would be faster, but if it is, it is not noticeable.

I desperately need help.

My code:
Objective: Move column f to column b. Data begins in row 8

Sub test()

Dim thelastrow as long
Dim irow as long

'find the last row
irow = cells(65536, "A").End(xlUp).Row
If irow > thelastrow Then thelastrow = irow

Range ("F8:F" & thelastrow).Select
Selection.Cut
Range("b8:b" & thelastrow).Select
Selection.Insert Shift:=xlToRight

End Sub

 

[lathare]

This is an old post, and I apologize I'm not certain what the board etiquette is for this. This answer is EXACTLY what I was also looking for, but I was wondering if there is a way to do this while also copying "dumb" cell coloring. I have cells shaded certain colors, and it is not conditional. I would like for the colors to move with the cells.
Thank you!

You changed something! Earlier you said you wanted to move data from Row 8 downward... now you are saying you want to move whole columns of your data. I added a StartRow constant (the Const statement) which will allow you to control this... I set it to 8 in the code below because that was your original request, but if you really want whole columns of your data instead, then just change the 8 to a 1 in that Const statement.

' [B][COLOR=darkred]NOTE: THIS CODE IS ONLY GOOD FOR CONSTANT DATA, NOT FORMULA DATA[/COLOR][/B]
Sub MoveColumnsAround()
  Dim NewColumnNumberOrder As String, LastRow As Long, Cols As Variant
  Const StartRow As Long = 8
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  Application.ScreenUpdating = False
 
  ' Column Letter Order:  D C B
  NewColumnNumberOrder = "4 3 2"
  Cols = Application.Index(Cells, Evaluate("Row(" & StartRow & ":" & LastRow & ")"), Split(NewColumnNumberOrder))
  Range("B" & StartRow & ":D" & LastRow).Clear
  Range("B" & StartRow).Resize(LastRow - StartRow + 1, UBound(Split(NewColumnNumberOrder)) + 1) = Cols
 
  ' Column Letter Order:  P  M  O  Q  R  N
  NewColumnNumberOrder = "16 13 15 17 18 14"
  Cols = Application.Index(Cells, Evaluate("Row(" & StartRow & ":" & LastRow & ")"), Split(NewColumnNumberOrder))
  Range("M" & StartRow & ":R" & LastRow).Clear
  Range("M" & StartRow).Resize(LastRow - StartRow + 1, UBound(Split(NewColumnNumberOrder)) + 1) = Cols
 
  Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

This is an old post, and I apologize I'm not certain what the board etiquette is for this. This answer is EXACTLY what I was also looking for, but I was wondering if there is a way to do this while also copying "dumb" cell coloring.

Not with the fast method that I used to move the values around. As far as I know, if you want to move any cell properties (color is a cell property) along with the values, this will have to be done individually (pretty much cell-by-cell) and that will slow things down quite a bit. While I am not sure if I'll be able to come up with anything quicker, it would help to know how many different colors maximum we are talking about and if they are pure colors or tints of some sort (your use of the term "shaded" could be interpreted either way).