Delete Multiple Rows using VBA – Excel Macro Example Code

Home/VBA/Delete Multiple Rows using VBA – Excel Macro Example Code

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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.

LIMITED TIME OFFER
By |April 21st, 2014|VBA|12 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

12 Comments

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

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

    • PNRao January 15, 2015 at 1:28 PM - Reply

      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!

  2. Leela February 15, 2015 at 5:57 AM - Reply

    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.

    • PNRao March 2, 2015 at 7:16 PM - Reply

      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!

  3. satish June 23, 2015 at 6:55 PM - Reply

    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

  4. claudio October 24, 2016 at 5:12 PM - Reply

    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.

    • PNRao October 24, 2016 at 9:28 PM - Reply

      Thanks for pointing out the scenario.

  5. Himanshu January 11, 2017 at 8:02 PM - Reply

    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.

  6. Himanshu January 11, 2017 at 8:09 PM - Reply

    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

  7. Himanshu January 11, 2017 at 8:10 PM - Reply

    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.

  8. Nandini February 7, 2017 at 8:24 PM - Reply

    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?

  9. Elton July 26, 2017 at 12:53 PM - Reply

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

Leave A Comment


Related pages


excel vba moduleexcel metrics dashboardvloopup exampleuserforms excelvlookup excel functioncopy worksheet vbasql query excelvlookup formula in excel 2003how to create excel worksheetpivot table templateupdate vba accessexcel hlookup functiontimevalue vbashared workbookmicrosoft excel remove hyperlinkhow to hide formula in excel 2007 without protecting sheetexcel vba msgbox optionsinsert new worksheet in excelapplication object vbamessagebox iconmicrosoft excel template downloadssumif excel formulahow to use name manager in excel 2007c language viva questions with answers pdfexcel vba case selectuses of vbaaverage function in excel 2010clustered cylinder chart excelproject task tracker excelvlookup left to rightexcel iferrorconstruction project template excelestimating spreadsheet templatehow to merge cells in excel 2007week planner excelgreater than excel formulaexcel formulas tutorial with exampleshow to create a shared workbook in excel 2010record excel macrovba instr examplehow to protect selected cells in excel 2007macro xlsms excel vlookup examplesexcel sample formulasinterview worksheetactivex checkexcel mark duplicatesexcel vba sortingexcel 2007 dashboards and reports for dummiesvba excel code listexcel color cell formularemove duplicate entries from excelvba userform closeinterview questions in testing for fresherssumif in excel 2007add clearing choice buttonunprotect an excel workbookvba controlsexcel colour chartvlookup excel samplehow to hide tabs in exceldelete a row in excelvba range cellsauto column width excelstacked pyramid chart excelselect sheet excel vbamacro to insert rowsdata validation in excel 2010excel tutorial for beginnersexcel 2007 vba pdfhow to delete unwanted rows in excelexcel sheet remove passwordmicrosoft excel substitute