VBA to insert and delete multiple rows

wilsonyeoh

New Member
Joined
May 25, 2009
Messages
17
Does anyone have codes to insert and delete multiple rows. I need to run a macro where a dialog box pops up requesting number of rows to insert and delete. Can anyone assist.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi, Try this:-
Code:
[COLOR="Blue"]Sub[/COLOR] MG30May14
[COLOR="Blue"]Dim[/COLOR] Rng [COLOR="Blue"]As[/COLOR] Range, Num [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String,[/COLOR] ans [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
[COLOR="Blue"]On[/COLOR] [COLOR="Blue"]Error[/COLOR] [COLOR="Blue"]Resume[/COLOR] [COLOR="Blue"]Next[/COLOR]
[COLOR="Blue"]Set[/COLOR] Rng = Application.InputBox(prompt:="Please [COLOR="Blue"]Select[/COLOR] Start Row ", Title:="Insert Rows", Type:=8)
    Num = Application.InputBox(prompt:="Please Insert Number of Rows", Title:="Insert Rows", Type:=1)
        [COLOR="Blue"]If[/COLOR] Num = False [COLOR="Blue"]Then[/COLOR] [COLOR="Blue"]Exit[/COLOR] [COLOR="Blue"]Sub[/COLOR]
        ans = MsgBox("Click Yes for ""Insert"",  No for ""Delete""", vbYesNo + vbInformation)
    
        [COLOR="Blue"]If[/COLOR] ans = vbYes [COLOR="Blue"]Then[/COLOR]
            Rng.Resize(Num).EntireRow.Insert
        [COLOR="Blue"]ElseIf[/COLOR] ans = vbNo [COLOR="Blue"]Then[/COLOR]
            Rng.Resize(Num).EntireRow.Delete
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hello Mike. Thanks for the code but can I have 2 separate buttons; one for insert and one for delete, and can you make the rows to insert from row 5 downwards. Hope it can be done.
 
Upvote 0
Hi, If you want buttons you will need a "Userform" which requires you to add it to you worksheet with the other controls to run the code.
Another option would be to add 2 items to the Worksheet menuBar, "Delete Rows" and "Insert Rows" and run the code from there.
This would just require you to add some Module and WorkBook Code.
Which way do you want to go.
Regards Mick
 
Upvote 0
Hi, Open the VB Window for your workbook. (Right click sheet tab and Select View Code).
Click "Ctrl+R" to view the Vb Project.
Open the "+" signs next to the Project List to show the Excel Objects, at the Bottom of the list you will see "ThisWorkBook", Double click. (New window on right opens.)
Paste the the code below, labeled "WorkBook" into this window.

For the "Module" codes do the following:-
On the Vb Toolbar , click "Insert", Module",(New Window appears)
Paste the first Module into the window, Repeat this operation for the second Module.
Close the work book and reopen it. The Tool bars should now have new menus.
Click to operate

"WorkBook"
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    On Error Resume Next
    Application.CommandBars("Worksheet Menu bar").Controls("Delete Rows").Delete
        Application.CommandBars("Worksheet Menu bar").Controls("Insert Rows").Delete

    On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim cControl    As CommandBarButton
    
    Set cControl = Application.CommandBars("Worksheet Menu bar").Controls.Add
    With cControl
        .Caption = "&Delete Rows"
        .Style = msoButtonCaption
        .OnAction = "Del"
    End With
Set cControl = Application.CommandBars("Worksheet Menu bar").Controls.Add
    With cControl
        .Caption = "&Insert Rows"
        .Style = msoButtonCaption
        .OnAction = "Ins"
    End With
End Sub

"Modules"
Code:
Sub Del()
 Dim Rng As Range, Num As String
On Error Resume Next
Set Rng = Application.InputBox(prompt:="Please Select Start Row ", Title:="Delete Rows", Type:=8)
    If Split(Rng.Address, "$")(2) < 5 Then
        MsgBox "Please choose a Number Greater than 4"
        Exit Sub
    End If
    Num = Application.InputBox(prompt:="Please Insert Number of Rows", Title:="Delete Rows", Type:=1)
        If Num = False Then Exit Sub
             Rng.Resize(Num).EntireRow.Delete
End Sub
Code:
Sub Ins()
 Dim Rng As Range, Num As String
On Error Resume Next
Set Rng = Application.InputBox(prompt:="Please Select Start Row ", Title:="Insert Rows", Type:=8)
 If Split(Rng.Address, "$")(2) < 5 Then
        MsgBox "Please choose a Number Greater than 4"
        Exit Sub
    End If
        Num = Application.InputBox(prompt:="Please Insert Number of Rows", Title:="Insert Rows", Type:=1)
          If Num = False Then Exit Sub
             Rng.Resize(Num).EntireRow.Insert
End Sub
Regards Mick
 
Upvote 0
Mick, my colleagues love your work and they think it's great however they decided that it would be best to put them as buttons on the worksheet rather than in the menubar. They also think that there should not be a request to select start row as it confuses the user (a dialog box appears with this statement "please choose a number greater than 4")when they unintentionally select between Row 1 and Row 4. Can you just code in such a way that the insertion and deletion starts at row 5.
 
Upvote 0
Hi, I think we've gone the full circle.
Run the first bit of the "ThisWorkBook" code to delete the new menus items.
Change the Command Button and "Insert/Delete" to suit.
Code:
Private [COLOR="Blue"]Sub[/COLOR] CommandButton1_Click()
 [COLOR="Blue"]Dim[/COLOR] Num [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
        Num = Application.InputBox(prompt:="Please Insert Number of Rows", Title:="Insert Rows", Type:=1)
[COLOR="SeaGreen"][B]             'Change this Line, at end, for "Delete" in Delete Code[/B][/COLOR]
             Rows(5).Resize(Num).EntireRow.Insert
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]
Regards Mick
 
Upvote 0
wilsonyeoh

Could you be more specific on where you actually want to insert/delete rows?

Also how many rows should be inserted/deleted?

Would that be determined by what rows the user has selected?

Or do you want to ask them for input?
 
Upvote 0
Hi Mick.

Run the first bit of the "ThisWorkBook" code to delete the new menus items.
Change the Command Button and "Insert/Delete" to suit.

Please give more details. I'm lost here. Seems like all my spreadsheet has those in the menubar now
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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