Macro question

Posted by Aysegul on October 23, 2001 5:02 AM

I am a novice when it comes to macros. I have following commands;
Rows ("2:2"). Select
selection.Intsertshift:=xldown
Range("F1:I1").Select
Selection.Cut
Range("B2").Select
ActiveSheet.Paste
Rows ("4:4"). Select
selection.Intsertshift:=xldown
Range("F3:I3").Select
Selection.Cut
Range("B4").Select
ActiveSheet.Paste
This commands are repeated until the end of the data by skipping 1 row each
time.
However, writing them in this way is totally inefficient. How can I get the
same results using much more efficient syntax.
Thanks for your help

Posted by Bob Umlas on October 23, 2001 5:42 AM

You can do at least 2 ways. The first is a loop of what YOU did, but there is a much faster way (although more code) to do it -- let me know if you want to know that way.
-------------------------------------------------
For i=1 to range("F65536").end(xlup).row Step 2
rows(i+1).Insert
Range("F" & i & ":I" & i).cut Range("B" & i+1)
Next

Posted by EDDIE G on October 23, 2001 5:56 AM

BOB - I WOULD LIKE THE FASTER METHOD

I am also interested in the solution to this problem. Can you post the faster method as well?

Posted by Geoffrey Hurst on October 23, 2001 5:57 AM

Try this :-

Sub Insert_Cut_Paste()
Dim rng As Range
Columns(1).Insert
Set rng = Range(Range("G1"), Range("G65536").End(xlUp)).Offset(0, -6)
With rng(1, 1)
.Value = 1
.AutoFill Destination:=rng, Type:=xlFillSeries
End With
rng.Copy rng.End(xlDown).Offset(1, 0)
Range(Cells(1, 1), Cells(1, 1).End(xlDown)).EntireRow.Sort Key1:=rng(1, 1)
Columns(1).Delete
Range(Range("F1"), Range("I65536").End(xlUp)).Cut Range("B2")
End Sub

Posted by Aysegul on October 23, 2001 9:12 AM

Thanks for the help. I am also curios of faster way. If you have time, I would appreciate the code.

Posted by . on October 23, 2001 12:46 PM

Here's a faster way ........

See Geoffrey Hurst's answer above

Posted by Francisco Gento on October 23, 2001 10:51 PM

Here's how fast ........

With the maximum possible data to process (that is, 32768 rows of data in columns F:I), the proposed faster macro takes about 2 seconds to run.
(Have not timed the loop macro for this amount of data, but there would probably be time to boil a kettle of water while it was running.)