HELP - ROW MACRO NEEDED
Posted by EDDIE G on October 06, 2001 12:18 PM
I have a sheet that has names in column A, numbers in column b and c. I need some kind of looping macro that replaces the below macro that i am currently using. It must insert a row starting at row 3, then cut the figure from C2 and paste it in B3. Then insert a row at row 5, cut C4 and paste it in B5, and so on and so forth all the way through row 1500. The below macro i am using is too long and takes too long to run. Can someone help?
Sub RowInserter ()
Rows("3:3").Insert Shift:=xlDown
[c2]cut [B3]
Rows("5:5").Insert Shift:=xlDown
[C4]cut [B5]
etc. etc. etc.
end sub
As you can see, if I do this through row 1501, its too long.
Posted by Henry Root on October 06, 2001 5:39 PM
You don't need a macro that loops to do this (would also be slow).
You can do it manually in less time than it would take to write a macro as follows :
- Insert a column before column C and fill with sequential numbers (starting with 1) down to the end of your data.
- Insert a column before column B and fill with sequential numbers (starting with 1) down to the end of your data.
- Copy the data in columns D:E to columns B:C at the first row below your data.
- Select columns A:C and sort by column B
- Delete columns B, D, and E.
You can create a macro for the above with the macro recorder. Here is a "cleaned up" macro (I first used the recorder to get most of the code and then revised the code) :-
Sub MergeColumns()
Dim rng As Range, nextBlank As Range
Set rng = Range(Range("A2"), Range("A65536").End(xlUp))
Columns("C").Insert
Range("C2").Value = 1
rng.Offset(0, 2).DataSeries
Columns(2).Insert
Range("B2").Value = 1
rng.Offset(0, 1).DataSeries
Set nextBlank = Range("A65536").End(xlUp).Offset(1, 1).Resize(1, 2)
Range(rng.Offset(0, 3), rng.Offset(0, 4)).Copy nextBlank
Columns("A:C").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
Range("B:B,D:E").Delete
End Sub
Posted by NOTE FOR HENRY ROOT on October 06, 2001 7:03 PM
THANKS HENRY ROOT WHAT IS YOUR EMAIL ADDRESS?
Posted by Henry Root on October 06, 2001 8:39 PM
Sorry, not for publication
