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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hello and welcome to MrExcel.

Try

Code:
Sub InsRow()
Dim iRow As Long
iRow = InputBox("Enter row number")
Rows(iRow).Insert
End Sub
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top