cut/paste columns efficiency
 

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

cut/paste columns efficiency
 

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

cut/paste columns efficiency
 

On Feb 2, 11:18*am, GS wrote:
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

--
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!

cut/paste columns efficiency
 

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

cut/paste columns efficiency
 

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)

cut/paste columns efficiency
 

On Feb 2, 11:36*am, "Rick Rothstein"
wrote:
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)

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!

cut/paste columns efficiency
 

Rick Rothstein laid this down on his screen :
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)

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

cut/paste columns efficiency
 

Matthew Dyer was thinking very hard :
On Feb 2, 11:36*am, "Rick Rothstein"
wrote:
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)

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...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

cut/paste columns efficiency
 

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

cut/paste columns efficiency
 

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

Sub Clear_Cut_PasteColumns()
Range("C:D").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)


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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

cut/paste columns efficiency
 

It happens that Rick Rothstein formulated :
I can save you two lines of code doing it this way...

Sub Clear_Cut_PasteColumns()
Range("C:D").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)


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

Gotta love it!<g

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

cut/paste columns efficiency
 

Rick Rothstein has brought this to us :
Sub Clear_Cut_PasteColumns()
Range("C:D").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:D").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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

cut/paste columns efficiency
 

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

cut/paste columns efficiency
 

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)