VBA code to Delete multiple rows example will help us to delete multiple rows from excel worksheet. We can use Delete method of Rows to delete the multiple rows. In this example we will see how to delete the multiple rows in excel worksheet using VBA. VBA code for deleting multiple rows macro should work for all the version of Microsoft Excel 2003, Excel 2007, Excel 2010, and Excel 2013.

VBA Delete Multiple Rows Excel Macro Example Code

VBA code to delete multiple rows


Here is the Example VBA syntax and Example VBA Macro to delete multiple rows from excel worksheets. This will help you to know how to delete specific and multiple rows from Excel workbook using VBA.

VBA Delete multiple rows: Syntax


Following is the VBA Syntax and sample VBA code to delete multiple rows from worksheet using VBA. We are using the Delete method of the Rows object of worksheet.


Rows(“

[Row Numbers]”).EntireRow.Delete

Here Row Numbers are your row numbers to delete. And EntireRow.Delete method will delete the Entire rows from the Excel spreadsheet.

Delete multiple rows using VBA: Examples


The following VBA code is to delete multiple rows from the worksheet. This code will delete the multiple rows (1 to 3) which we have mentioned in the code.

Sub sbVBS_To_Delete_Multiple_Rows ()
    Rows(“1:3”).EntireRow.Delete
End Sub

Instructions to run the VBA code to delete multiple rows


Please follow the below steps to execute the VBA code to delete multiple rows from worksheets.
Step 1: Open any existing Excel workbook
Step 2: Press Alt+F11 – This will open the VBA Editor
Step 3: Insert a code module from then insert menu
Step 4: Copy the above code and paste in the code module which have inserted in the above step
Step 5: enter some sample data in row 1 to 5
Step 6: Now press F5 to execute the code

Now you can observe that the entire rows from 1 to 3 are deleted from worksheet.

Explained VBA Code to Delete Multiple Rows:


‘Starting program and sub procedure to write VBA code to delete entire row from sheet
Sub sbVBS_To_Delete_Multiple_Rows_C()

‘Specifying the Rows to delete and Deleting the Rows using EntireRow.Delete method.
Rows(“1:3”).EntireRow.Delete

‘Ending the sub procedure to delete entire row
End Sub

Here Rows(“1:3”) is to tell excel to delete rows from 1 to 3 of the worksheet. And Delete method will delete the all specified rows form the worksheet.

120+ Professional Project Management Templates!
Save Up to 85% LIMITED TIME OFFER

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Browse All Templates
Excel VBA Project Management Templates

All-in-One Pack
120+ Project Management
Premium Templates
View Details

Essential Pack
50+ Project Management
Premium Templates
View Details
50+ Excel
Project Management
Templates Pack
View Details
50+ PowerPoint
Project Management
Templates Pack
View Details
25+ MS Word
Project Management
Templates Pack
View Details
Ultimate Project Management Template
View Details
Ultimate Resource Management Template
View Details
Project Portfolio Management Templates
View Details
By Last Updated: June 17, 2022Categories: VBATags:

Share This Story, Choose Your Platform!

16 Comments

  1. yogesh January 14, 2015 at 2:23 AM

    how to delete (suppose 1-10 rows) in multiple CSV simultaneously (for folder option)

  2. PNRao January 15, 2015 at 1:28 PM

    Hello Yogesh,
    We can do this, you can open the csv files in Excel using VBA and then delete the rows using Rows(“1:10”).Delete method.

    Steps:
    -loop though the folder
    – if you have multiple types of files, check if the file is in your desired format (.csv)
    – open the csv file in Excel
    -Delete the Rows 1 to 10

    You can find the detailed examples in our 100+ useful macros list.

    Hope this helps! Thanks-PNRao!

  3. Leela February 15, 2015 at 5:57 AM

    Hi there,

    What about deleting random rows of data in a data set? For example, if I have say rows 2,4,5,8,12 to delete (delete line of code included in a macro), there is always one left behind and I need to delete this separately. The random rows are selected using a criteria in the macro.

    Thanks in advance.

  4. PNRao March 2, 2015 at 7:16 PM

    Hi Leela,

    You can store the random rows in an array or worksheet and delete by looping through these items.

    arrDel=Array(2,4,5,8,12)
    arrDel=Array(12,8,5,4,2)
    For i=0 to UBound(arrDel,1)
       Rows(arrDel(i)).Delete
    Next
    

    Thanks-PNRao!

  5. satish June 23, 2015 at 6:55 PM

    i download a report in notepad, copy it to excel and delete extra columns and headers. i am in process of automating the excel report. how to delete random rows for example , headers like “—–“, “Program” etc., kindly help me

  6. claudio October 24, 2016 at 5:12 PM

    Note:, if done in loop then it is wrong to do it wiht “arrDel=Array(2,4,5,8,12)” — not in ascending order

    Do it in descending order: arrDel=Array(12,8,5,4,2)

    It is important, because: after you delete row 2, what was on row 4 is now on row 3, etc.

    So dong it with arrDel=Array(2,4,5,8,12) you will be deleting the original rows 2,5,7,11,15, this is because:
    -After deleting 2, the rest that had higher number are in one less, so the row number 5 becomes the number 4, and so on
    -The after deleting what now is row number 4 (old row number five), all rows with number higher than 4 will be in one row less, so original 8 will be on row number 6 (two rows have been deleted, so substract 2)
    -And so on

    If done in descending order, deleting the highest row number will not affect the rest of rows number that want to be deleted, since they have a lower row number than the deleted one.

    Always have in mind: deleting one row, moves all the higher rows one number done.

  7. PNRao October 24, 2016 at 9:28 PM

    Thanks for pointing out the scenario.

  8. Himanshu January 11, 2017 at 8:02 PM

    How can a selection of rows on the worksheet be deleted. Can we use
    “Activesheets(“Sheet1″).rows(12,8,5,4,2).select

    Selection.rows.entirerow.delete ”

    as a valid command.

  9. Himanshu January 11, 2017 at 8:09 PM

    Sir, pl explain the function of Ubound() in the below sub. is it taking the largest no in the array and then reducing by 1? Thanks

    arrDel=Array(12,8,5,4,2)
    For i=0 to UBound(arrDel,1)
    Rows(arrDel(i)).Delete
    Next

  10. Himanshu January 11, 2017 at 8:10 PM

    Last one. How to code if we have to select rows on the worksheet and want those to be deleted using vba function. pl help thanks.

  11. Nandini February 7, 2017 at 8:24 PM

    Hi, what if you want to delete a set of rows satisfying some criteria for example, rows that have “calendar”text in the first column?

  12. Elton July 26, 2017 at 12:53 PM

    what if I want to delete every 55th to 61st rows starting with the active cell?

  13. Bilney July 18, 2018 at 8:04 PM

    how about deleting a range of rows like below but that number changes everyday? & the date changes to previous working day?

    Sub Delete_Rows()

    ‘ Delete_Rows Macro


    Selection.AutoFilter
    ActiveSheet.Range(“$A$1:$F$1403”).AutoFilter Field:=1, Criteria1:=Array( _
    “BRANCH 301111 29JN18”, _
    “BRANCH 301111 PORTLAND (301111)”, _
    “TEL CB OVRD CAL-DT TR-CODE ORIG TIME”, “=”), Operator:= _
    xlFilterValues
    Rows(“39:1362”).Select
    Selection.Delete Shift:=xlUp
    End Sub

  14. manish September 7, 2018 at 3:31 PM

    Sub DelRows()

    ‘ DelRows Macro
    ‘ delete specified rows

    ‘ Keyboard Shortcut: Ctrl+d

    Dim fromrow As String
    Dim torow As String

    On Error Resume Next ‘Blanket error handling

    ‘Display inputbox to prompt user for row/column
    fromrow = InputBox(“Type a row number from where to delete”)
    torow = InputBox(“Type a row number upto where to delete”)
    Rows(“fromrow:torow”).EntireRow.Delete

    End Sub

  15. manish September 7, 2018 at 3:32 PM

    Sub DelRows()

    ‘ DelRows Macro
    ‘ delete specified rows

    ‘ Keyboard Shortcut: Ctrl+d

    Dim fromrow As String
    Dim torow As String

    On Error Resume Next ‘Blanket error handling

    ‘Display inputbox to prompt user for row/column
    fromrow = InputBox(“Type a row number from where to delete”)
    torow = InputBox(“Type a row number upto where to delete”)
    Rows(“fromrow#:torow#”).EntireRow.Delete
    End Sub

    i made this function on marco but doesnt working

  16. Amin September 6, 2020 at 10:41 AM

    Hi
    I have a VBA code that sorts and filters data from one excel table and save 48 different reports on my desktop. but based on those filters, some generated reports have only 1 row (headers) and no data. How can I add some VBA code to my file that prevents to save files that has just one row (header) and no data?
    Thank you

Leave A Comment