Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: insert rows anywhere in the sheet
I'm rather new to macro-programming...
I want a macro to insert blank rows beneath the active row (or cell) and then copy the active row to the first blank row beneath the row with content. By using "Register macro" I have made a macro like this: Sub Makro1() ' ' Makro1 Makro ' ' Hurtigtast: CTRL+m ' Rows("6:10").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows("5:5").Select Selection.Copy Rows("6:6").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub The problem is that if the cursor is pointing at for instance row 12, and I activate the macro by ctrl+m, the blank rows still are inserted beneath row 5, not beneath row 12. Kjell |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: insert rows anywhere in the sheet
You may want to look at David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm look for: InsertRowsAndFillFormulas On 06/25/2010 01:52, excel wrote: I'm rather new to macro-programming... I want a macro to insert blank rows beneath the active row (or cell) and then copy the active row to the first blank row beneath the row with content. By using "Register macro" I have made a macro like this: Sub Makro1() ' ' Makro1 Makro ' ' Hurtigtast: CTRL+m ' Rows("6:10").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows("5:5").Select Selection.Copy Rows("6:6").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub The problem is that if the cursor is pointing at for instance row 12, and I activate the macro by ctrl+m, the blank rows still are inserted beneath row 5, not beneath row 12. Kjell -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: insert rows anywhere in the sheet
Hi
Try Sub Makro1() ' Makro1 Makro ' Hurtigtast: CTRL+m Dim ar As Long ar = ActiveCell.Row Rows(ar & ":" & ar + 4).Insert Shift:=xlDown, _ CopyOrigin:=xlFormatFromLeftOrAbove Rows(ar - 1).Copy Rows(ar) Application.CutCopyMode = False End Sub -- Regards Roger Govier "excel" <kjeblo(())frisurf.no wrote in message ... I'm rather new to macro-programming... I want a macro to insert blank rows beneath the active row (or cell) and then copy the active row to the first blank row beneath the row with content. By using "Register macro" I have made a macro like this: Sub Makro1() ' ' Makro1 Makro ' ' Hurtigtast: CTRL+m ' Rows("6:10").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows("5:5").Select Selection.Copy Rows("6:6").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub The problem is that if the cursor is pointing at for instance row 12, and I activate the macro by ctrl+m, the blank rows still are inserted beneath row 5, not beneath row 12. Kjell __________ Information from ESET Smart Security, version of virus signature database 5228 (20100625) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 5228 (20100625) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: insert rows anywhere in the sheet
Thanks Roger. That solved my problem!
Kjell "Roger Govier" skrev i melding ... Hi Try Sub Makro1() ' Makro1 Makro ' Hurtigtast: CTRL+m Dim ar As Long ar = ActiveCell.Row Rows(ar & ":" & ar + 4).Insert Shift:=xlDown, _ CopyOrigin:=xlFormatFromLeftOrAbove Rows(ar - 1).Copy Rows(ar) Application.CutCopyMode = False End Sub -- Regards Roger Govier "excel" <kjeblo(())frisurf.no wrote in message ... I'm rather new to macro-programming... I want a macro to insert blank rows beneath the active row (or cell) and then copy the active row to the first blank row beneath the row with content. By using "Register macro" I have made a macro like this: Sub Makro1() ' ' Makro1 Makro ' ' Hurtigtast: CTRL+m ' Rows("6:10").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Rows("5:5").Select Selection.Copy Rows("6:6").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub The problem is that if the cursor is pointing at for instance row 12, and I activate the macro by ctrl+m, the blank rows still are inserted beneath row 5, not beneath row 12. Kjell __________ Information from ESET Smart Security, version of virus signature database 5228 (20100625) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 5228 (20100625) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: insert rows anywhere in the sheet
Here's a generic proc you can modify to suit or keep as a reusable
utility: Public Sub InsertBlankRows(Optional Position As String) ' Inserts a specified number of rows at the location specified. ' If the Position arg is not used then the default is ActiveCell.Row. Dim vRows As Variant, lPos As Long Const sMsg As String = "Enter the number of rows to insert." 'Evaluate user input On Error Resume Next vRows = InputBox(Prompt:=sMsg, Default:=1) If vRows = "" Then Exit Sub '//user cancels If Not Err = 0 Or _ Not IsNumeric(vRows) Or _ Not vRows = 1 Then Exit Sub 'Get the position to insert lPos = ActiveCell.Row If Position = "Below" Then lPos = lPos + 1 'Insert the rows Cells(lPos, 1).Resize(vRows).Insert Shift:=xlDown Application.CutCopyMode = False End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to insert value from range on different sheet and copy sheet | Excel Programming | |||
Prevent insert/delete rows - sheet code | Excel Programming | |||
How do you insert rows in a sheet that has a PivotTable | Excel Programming | |||
Insert Rows in Balance Sheet Template | New Users to Excel | |||
In Excell sheet i want to insert more than 65,536 rows how i can . | Excel Worksheet Functions |