cut/paste columns efficiency

[Matthew Dyer]

I am currently clearing/cutting/pasting columns using the below code.
Is there a more efficient way of doing this?

Columns("A:B").ClearContents
Columns("E:H").ClearContents
Columns("J").ClearContents
Columns("M:Q").ClearContents
Columns("T").ClearContents
Columns("V:Z").ClearContents
Columns("AC").ClearContents
Columns("I").Cut
Range("E1").Select
ActiveSheet.Paste
Columns("AA:AB").Cut
Range("F1").Select
ActiveSheet.Paste
Columns("K:L").Cut
Range("H1").Select
ActiveSheet.Paste
Columns("U").Cut
Range("J1").Select
ActiveSheet.Paste
Columns("C:J").Cut
Range("A1").Select
ActiveSheet.Paste
Columns("S").Cut
Range("I1").Select
ActiveSheet.Paste
Columns("R").Cut
Range("J1").Select
ActiveSheet.Paste

 

[GS]

Matthew Dyer wrote :

I am currently clearing/cutting/pasting columns using the below code.
Is there a more efficient way of doing this?

Columns("A:B").ClearContents
Columns("E:H").ClearContents
Columns("J").ClearContents
Columns("M:Q").ClearContents
Columns("T").ClearContents
Columns("V:Z").ClearContents
Columns("AC").ClearContents
Columns("I").Cut
Range("E1").Select
ActiveSheet.Paste
Columns("AA:AB").Cut
Range("F1").Select
ActiveSheet.Paste
Columns("K:L").Cut
Range("H1").Select
ActiveSheet.Paste
Columns("U").Cut
Range("J1").Select
ActiveSheet.Paste
Columns("C:J").Cut
Range("A1").Select
ActiveSheet.Paste
Columns("S").Cut
Range("I1").Select
ActiveSheet.Paste
Columns("R").Cut
Range("J1").Select
ActiveSheet.Paste

This looks like macro recorder junk. Select is not necessary. Use the
Destination option for the Cut method as follows...

Columns("I").Cut Columns("E")
Columns("AA:AB").Cut Columns("F")
...and so on

 

[Matthew Dyer]

Matthew Dyer wrote :







This looks like macro recorder junk. Select is not necessary. Use the
Destination option for the Cut method as follows...

  Columns("I").Cut Columns("E")
  Columns("AA:AB").Cut Columns("F")
  ...and so on

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

It is macro recorded processes, which i know is not the most efficient
method of programming to say the least. Thanks for the Destination
options!

 

[GS]

Here's some code to handle clearing the columns...

Const sColsToClear As String = "A:B,E:H,J,M:Q,T,V:Z,AC"
Dim s As Variant
For Each s In Split(sColsToClear, ",", , vbTextCompare)
Columns(s).Clear
Next

 

[Rick Rothstein]

Here's some code to handle clearing the columns...

Const sColsToClear As String = "A:B,E:H,J,M:Q,T,V:Z,AC"
Dim s As Variant
For Each s In Split(sColsToClear, ",", , vbTextCompare)
Columns(s).Clear
Next

If you fill out those single column references to double references (for
example, change the J to J:J, the T to T:T, etc.), then you can clear the
columns more efficiently with this one-liner...

Range("A:B,E:H,J:J,M:Q,T:T,V:Z,AC:AC").Clear

Rick Rothstein (MVP - Excel)

 

[Matthew Dyer]

If you fill out those single column references to double references (for
example, change the J to J:J, the T to T:T, etc.), then you can clear the
columns more efficiently with this one-liner...

Range("A:B,E:H,J:J,M:Q,T:T,V:Z,AC:AC").Clear

Rick Rothstein (MVP - Excel)

I knew there were easier ways to do this stuff. This is an old macro I
coded a while ago when I was just starting. It worked and I saw no
real need to delve into ways to do certiain things more efficiently,
but I've got some spare time now and am building on previous
knowledge. Thanks for all the help guys!

 

[GS]

Rick Rothstein laid this down on his screen :

If you fill out those single column references to double references (for
example, change the J to J:J, the T to T:T, etc.), then you can clear the
columns more efficiently with this one-liner...

Range("A:B,E:H,J:J,M:Q,T:T,V:Z,AC:AC").Clear

Rick Rothstein (MVP - Excel)

Rick.., you shine! You just always shine!<g>

 

[GS]

Matthew Dyer was thinking very hard :

I knew there were easier ways to do this stuff. This is an old macro I
coded a while ago when I was just starting. It worked and I saw no
real need to delve into ways to do certiain things more efficiently,
but I've got some spare time now and am building on previous
knowledge. Thanks for all the help guys!

You're welcome!
See Rick's better solution for clearing the columns...

 

[GS]

I found that using Columns for this line doesn't work:

Columns("C:J").Cut Columns("A")
..and so why not just delete columns("A:B")

Also, moving columns("S,R") to columns("K,L") aligns the same in the
end. Here's the resulting code:

Sub Clear_Cut_PasteColumns()
Range("A:B,E:H,J:J,M:Q,T:T,V:Z,AC:AC").Clear
Columns("I").Cut Columns("E")
Columns("AA:AB").Cut Columns("F")
Columns("K:L").Cut Columns("H")
Columns("U").Cut Columns("J")
Columns("S").Cut Columns("K")
Columns("R").Cut Columns("L")
Columns("A:B").Delete
End Sub

 

[Rick Rothstein]

I can save you two lines of code doing it this way...

Sub Clear_Cut_PasteColumns()
Range("C").Copy Range("A1")
Range("I:I,AA:AB").Copy Range("C1")
Range("K:L,U:U").Copy Range("F1")
Columns("S").Copy Range("I1")
Columns("R").Copy Range("J1")
Range("K:AB").Clear
End Sub

Rick Rothstein (MVP - Excel)

 

[GS]

It happens that Rick Rothstein formulated :

I can save you two lines of code doing it this way...

Sub Clear_Cut_PasteColumns()
Range("C").Copy Range("A1")
Range("I:I,AA:AB").Copy Range("C1")
Range("K:L,U:U").Copy Range("F1")
Columns("S").Copy Range("I1")
Columns("R").Copy Range("J1")
Range("K:AB").Clear
End Sub

Rick Rothstein (MVP - Excel)

Gotta love it!<g>

P.S.: ColAC is the last col to clear.

 

[GS]

Rick Rothstein has brought this to us :

Sub Clear_Cut_PasteColumns()
Range("C").Copy Range("A1")
Range("I:I,AA:AB").Copy Range("C1")
Range("K:L,U:U").Copy Range("F1")
Columns("S").Copy Range("I1")
Columns("R").Copy Range("J1")
Range("K:AB").Clear
End Sub

Saving 2 more lines...

Sub Clear_Cut_PasteColumns()
Range("C").Copy Range("A1")
Range("I:I,AA:AB").Copy Range("C1")
Range("K:L,U:U,S:S,R:R").Copy Range("F1")
Range("K:AB").Clear
End Sub

 

[GS]

Oops! Not quite the same results. Seems this doesn't follow the array
order! Phooey!

 

[Rick Rothstein]

Oops! Not quite the same results. Seems this doesn't follow

the array order! Phooey!

Yep, that is the problem (I crafted my solution to avoid that problem and
the problem of copying over of cell within the specified range)... otherwise
this could have been done in three lines of code.

Rick Rothstein (MVP - Excel)