Insert Row using Macro

naruale

New Member
Joined
Nov 27, 2009
Messages
7
Hey there!! I have just joined this forum. I hope you guys may be able to help me. Heres my question.

I hav a macro to insert a new row. But i also want the macro to ask the user to input where he wants(which row number) to insert the new row??

For example when i run the macro, an inputbox should come up asking to input the row number. The user should be able to enter the row number (take 10 for now). And Excel should automatically insert a new row in row number 10. So the previous row 10 now becomes row 11.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hello and welcome to MrExcel.

Try

Code:
Sub InsRow()
Dim iRow As Long
iRow = InputBox("Enter row number")
Rows(iRow).Insert
End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Here you are:

Code:
Sub Test()
    Dim r
    r = Application.InputBox(Prompt:="Enter row number", Title:="Insert row", Default:=ActiveCell.Row, Type:=1)
    If r = False Then Exit Sub
    Rows(r).Insert
End Sub
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
If you want to maintain the Formating of the above rows, it can be done using this:
Code:
Sub AddARow()
 Dim varUserInput As Variant
 varUserInput = InputBox("Enter Row Number where you want to add a row:", _
  "What Row?")
 If varUserInput = "" Then Exit Sub

RowNum = varUserInput
    Rows(RowNum & ":" & RowNum).Insert Shift:=xlDown
    Rows(RowNum - 1 & ":" & RowNum - 1).Copy Range("A" & RowNum)
    Range(RowNum & ":" & RowNum).ClearContents
End Sub
 

naruale

New Member
Joined
Nov 27, 2009
Messages
7

ADVERTISEMENT

thank you very much sir
 

naruale

New Member
Joined
Nov 27, 2009
Messages
7
Thank you very much sir. But I can ask you for another favour. Your codes insert a new row and also maintains the formatting but it does not copy the formulas from above row. Can you do it sir? Thank you again.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Try

Code:
Sub InsRow()
Dim iRow As Long
iRow = InputBox("Enter row number")
Rows(iRow).Insert
Rows(iRow - 1).Copy
With Range("A" & iRow)
    .PasteSpecial Paste:=xlPasteFormulas
    .PasteSpecial Paste:=xlPasteFormats
End With
End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Another possibility:

Code:
Sub Test()
    Dim r
    r = Application.InputBox(Prompt:="Enter row number", Title:="Insert row", Default:=ActiveCell.Row, Type:=1)
    If r = False Then Exit Sub
    Rows(r - 1).Copy
    Rows(r).Insert
End Sub
 

naruale

New Member
Joined
Nov 27, 2009
Messages
7
Deep thanks to all sirs..

But still what excel is doing is copy everything including datas (e.g. names, formulas) from the above row. So I am getting 2 rows with all the data and formulas same. I dont want that.

I only want the formulas and formats to be copied, not the datas such as names.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
Sub Test()
    Dim r
    r = Application.InputBox(Prompt:="Enter row number", Title:="Insert row", Default:=ActiveCell.Row, Type:=1)
    If r = False Then Exit Sub
    Rows(r - 1).Copy
    Rows(r).Insert
    On Error Resume Next
    Rows(r).SpecialCells(xlCellTypeConstants).ClearContents
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,780
Messages
5,598,038
Members
414,205
Latest member
Tushark

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top