Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert rows evry other time
Hi!
I have a large database in Excel and I need to insert a row every other time. When I record a macro in the normal way (by recording what I do) and then run it, the macro always return to the rows I selected when I recorded the macro and insert the rows there. I need the macro to continue to the end of the database selecting every other row without specifiing which row it is. Can anyone out there help me with this as fast as possible? Many thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert rows evry other time
Try this:-
Sub Inserteveryother() Set rng1 = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)) col = ActiveCell.Column For i = rng1(rng1.Rows.Count).row To rng1(1).row + 1 Step -1 Cells(i, col).Insert Shift:=xlDown Next i End Sub Select the top cell of a column that has your data in and run this. Mike "JoiSim" wrote: Hi! I have a large database in Excel and I need to insert a row every other time. When I record a macro in the normal way (by recording what I do) and then run it, the macro always return to the rows I selected when I recorded the macro and insert the rows there. I need the macro to continue to the end of the database selecting every other row without specifiing which row it is. Can anyone out there help me with this as fast as possible? Many thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert rows evry other time
Sorry JoiSim,
Try this instead:- Sub Inserteveryother() Set rng1 = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)) col = ActiveCell.Column For i = rng1(rng1.Rows.Count).row To rng1(1).row + 1 Step -1 Rows(i).Select Selection.Insert Shift:=xlDown Next i End Sub "JoiSim" wrote: Hi! I have a large database in Excel and I need to insert a row every other time. When I record a macro in the normal way (by recording what I do) and then run it, the macro always return to the rows I selected when I recorded the macro and insert the rows there. I need the macro to continue to the end of the database selecting every other row without specifiing which row it is. Can anyone out there help me with this as fast as possible? Many thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert rows evry other time
I have a technique which will do this faster than a macro, once it's set up:
If your range goes from A1:J2000, for example, then in K1 enter 1, in K2 enter 2, select K1:K2, double-click the fill handle so you now have numbers 1 thru 2000 in K1:K2000. Copy this range, use ctrl/down arrow (taking you to K2000), then down arrow to select K2001, then paste. You now have 2 sets of #s from 1 to 2000. Select all the columns from K to A (selecting them right-to-left keeps K1 active), then click the sort ascending toolbar button. Done! The blank rows from row 2001+ are sorted into place. Now you can clear column K. Tip #68 from my book, "This isn't Excel, it's Magic" "JoiSim" wrote in message ... Hi! I have a large database in Excel and I need to insert a row every other time. When I record a macro in the normal way (by recording what I do) and then run it, the macro always return to the rows I selected when I recorded the macro and insert the rows there. I need the macro to continue to the end of the database selecting every other row without specifiing which row it is. Can anyone out there help me with this as fast as possible? Many thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert rows evry other time
Thanks very much, this was brilliant! I don't know why I didn't think of
this, it is so easy when you know... like most things. Tanks again JoiSim "Bob Umlas" wrote: I have a technique which will do this faster than a macro, once it's set up: If your range goes from A1:J2000, for example, then in K1 enter 1, in K2 enter 2, select K1:K2, double-click the fill handle so you now have numbers 1 thru 2000 in K1:K2000. Copy this range, use ctrl/down arrow (taking you to K2000), then down arrow to select K2001, then paste. You now have 2 sets of #s from 1 to 2000. Select all the columns from K to A (selecting them right-to-left keeps K1 active), then click the sort ascending toolbar button. Done! The blank rows from row 2001+ are sorted into place. Now you can clear column K. Tip #68 from my book, "This isn't Excel, it's Magic" "JoiSim" wrote in message ... Hi! I have a large database in Excel and I need to insert a row every other time. When I record a macro in the normal way (by recording what I do) and then run it, the macro always return to the rows I selected when I recorded the macro and insert the rows there. I need the macro to continue to the end of the database selecting every other row without specifiing which row it is. Can anyone out there help me with this as fast as possible? Many thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to insert rows evry other time
Thanks very much. It works!
I got another andswer that is also very brilliant which dosn't need macro. I thougt myself that it wasn't possible whithout macro but... Thanks for your good work. Regards, JoiSim "Mike H" wrote: Sorry JoiSim, Try this instead:- Sub Inserteveryother() Set rng1 = Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)) col = ActiveCell.Column For i = rng1(rng1.Rows.Count).row To rng1(1).row + 1 Step -1 Rows(i).Select Selection.Insert Shift:=xlDown Next i End Sub "JoiSim" wrote: Hi! I have a large database in Excel and I need to insert a row every other time. When I record a macro in the normal way (by recording what I do) and then run it, the macro always return to the rows I selected when I recorded the macro and insert the rows there. I need the macro to continue to the end of the database selecting every other row without specifiing which row it is. Can anyone out there help me with this as fast as possible? Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Time Stamp when macro is run | Excel Discussion (Misc queries) | |||
How do I insert rows in more than one worksheet at a time? | Excel Worksheet Functions | |||
Excel Macro to insert rows | Excel Discussion (Misc queries) | |||
asking again, macro to insert rows | Excel Worksheet Functions | |||
insert rows in excel spreadsheet via macro | Excel Worksheet Functions |