Delete Entire Row using VBA – Excel Macro Example Code

Home/VBA/Delete Entire Row using VBA – Excel Macro Example Code

Excel VBA code to Delete Entire Row example will help us to delete rows in excel worksheet. We can use Delete method of Rows to delete the Entire Row. In this example we will see how to delete the entire row in excel worksheet using VBA. Excel VBA Macro code for deleting entire 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 Entire Row


Here is the VBA delete entire row macro Syntax and Example VBA Macro code to Delete Entire Row from excel worksheets. This will help you to know how to delete entire row from Excel workbook using VBA.

VBA Delete Entire Row: Syntax


Following is the VBA delete entire row macro Syntax and sample VBA macro command to Delete Entire Row from worksheet using VBA. We are using the Delete method of the Rows object of worksheet.


Rows(

[Row Number]).EntireRow.Delete

Here Row Number is your row number to delete. And EntireRow.Delete method will Delete Entire Row from the Excel spreadsheet.

Delete Entire Row using VBA: Examples


The following Excel VBA delete entire row macro code is to delete entire row from the worksheet. This VBA delete entire row macro will delete row which we have mentioned in the code.

Sub sbVBS_To_Delete_EntireRow ()
Rows(1).EntireRow.Delete
End Sub

Instructions to run the VBA Macro code to delete Entire Row


Please follow the below steps to execute the VBA delete entire row macro.
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.
Step 6: Now press F5 to execute the code.

Now you can observe that the entire row 1 is deleted from worksheet.

Explained VBA Code to Delete Entire Row:

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

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

‘Ending the sub procedure to delete entire row.
End Sub

Here Rows(1) is to tell excel to delete row 1 of the worksheet. And Delete method will delete entire row form the worksheet.

VBA to Delete Entire Row in Excel :


If you want to delete the first few rows in excel, you can repeat the delete command several times. The following VBA delete entire row macro code example will show you how to delete the first 3 rows in the worksheet.

Sub sbVBS_To_Delete_FirstFewRows_in_Excel()
Rows(1).EntireRow.Delete
Rows(1).EntireRow.Delete
Rows(1).EntireRow.Delete
End Sub

In this example, you can see that the delete command is repeated 3 times. It will first delete the first row, when we delete the first row. The next row becomes (2nd Row) the first row. So if you delete the first row again. The next row (3rd Row) becomes the first row. That means, we have deleted the first three rows using VBA.

VBA to Delete Entire Row in Excel : VBA code Explained


Here is the explanation of the VBA delete entire rowMacro to Delete first three rows in Excel.

Starting the procedure to write VBA commands to delete first few (3) rows.
Sub sbVBS_To_Delete_FirstFewRows_in_Excel_C()

‘First row is deleting
Rows(1).EntireRow.Delete
‘Now Second row becomes the first row- again we are deleting the first row.
Rows(1).EntireRow.Delete
‘Now Third row becomes the first row- again delete command is deleting the first row.
Rows(1).EntireRow.Delete
‘We have deleted the first 3 rows, and ending the sub procedure here.
End Sub

VBA to Delete Entire Row in Excel : Using For Loop


Here is the example VBA macro to delete the first few rows using For Loop. This will delete the first 10 records using For loop in VBA.

Sub sbVBS_To_Delete_EntireRow_For_Loop()
Dim iCntr
For iCntr = 1 To 10 Step 1
Rows(1).EntireRow.Delete
Next
End Sub

VBA to Delete Entire Row in Excel : Using For Loop – Explanation


Here is the detailed explanation of the delete row using for loop in VBA.

Starting the sub procedure to write VBA macro to delete rows using VBA for loop.
Sub sbVBS_To_Delete_EntireRow_For_Loop_C()
‘Declaring the variable iCntr to store the for loop iterations.
Dim iCntr
‘looping through the rows using for loop, here step statement indicates the iCntr increment.
For iCntr = 1 To 10 Step 1
‘deleting the first row.
Rows(1).EntireRow.Delete
Next
‘We have delete the first row 10 times, that means we have deleted the first 10 rows in excel sheet using for loop and VBA.
End Sub

VBA to Delete Entire Row in Excel : Using Do while Loop


Here is the example VBA delete entire row macro to delete the first few rows using Do while Loop. This will delete the first 10 records using For loop in VBA.

Sub sbVBS_To_Delete_EntireRow_Do_while_Loop()
Dim iCntr
iCntr = 10 ' Last row
Do While iCntr >= 1
Rows(iCntr).EntireRow.Delete
iCntr = iCntr - 1
Loop
End Sub

VBA to Delete Entire Row in Excel : Using Using Do while Loop – Explanation


Here is the explanation to the above VBA delete entire row Macro to delete Rows using Do while loop.

Starting the VBA macro to delete the rows using do while loop.
Sub sbVBS_To_Delete_EntireRow_Do_while_Loop()
‘Declaring the variable store the last row number and use it as counter variable.
Dim iCntr
‘assigning the last row value to iCntr.
iCntr = 10 ‘ Last row
‘Using do while loop we are deleting the rows from last (10th) row .
Do While iCntr >= 1
Rows(iCntr).EntireRow.Delete
iCntr = iCntr – 1
Loop
‘ending the sub procedure here.
End Sub

Here you can observe that we are looping through the rows from last to beginning. This will be the accurate code while deleting the rows from worksheet. Always delete row from last to first.

LIMITED TIME OFFER
By |April 21st, 2014|VBA|3 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).

3 Comments

  1. Mohamed November 27, 2015 at 5:46 PM - Reply

    Hi
    Thanks for deleting rows information. But I didn’t get the information what I am searching for.
    How to delete the rows upon selection by mouse click? If I select a single and run a code it will delete a row. Also if I select multiple rows and run a code it will delete rows how many I selected.
    I had an idea to create command button to delete selected rows by click on it.
    How to achieve this? Thanks in advance.

    • Bram Vreugdenhil August 18, 2016 at 1:37 PM - Reply

      A little late but you can delete a row of a selection with the following code

      Sub DeleteSelectedRows()
      Selection.EntireRow.Delete
      Application.CutCopyMode = False
      End Sub

  2. Subhash June 24, 2017 at 1:59 PM - Reply

    While using loop for deleting rows, it should have a row range (e.g. row 1, row 2….etc) selection preference for deletion. any code modification bout that

Leave A Comment


Related pages


excel popupexcel listboxhow to insert a new line in excel cellexcel vba status bar progressexcel macros advancedcool excel dashboardsexcel vba undosql aptitude questions with answers pdfexcel sort macrovba code for excelremove duplicate lines in excelexcel remove duplicates based on two columnstimeline in excel templateexcel.workbooks.addsample dashboards in excelproject timeline template pptvba dim functionconnect excel to sql serverexcel sort macrofind and delete duplicates in excelexcel 2010 unprotectexcel vba open pdf fileunix sas tutorialvba to open excel filecolor vbaexcel vba boldmysql oledb connection stringifs excel formulahow to highlight column in excelvlookup in excel definitionexcel count duplicatetableau tutorial for beginners pdfvba progress bar exampleexcel look upexcel vba selectexcel executive dashboardexample of vlookup in excel 2010types of variables in vbahow to unlock the excel filemacro excel 2007 tutorialunhide first columnhow to copy a worksheet in excelaria charts 2010if iserror functionhow do i create a pivot table in excel 2007genpact finance interview questionsexcel convert to lower caseexcel and word tests for interviewhow to open excel workbooksql interview question pdfactivesheet vbahow to draw pie chart in excel 2010not equal vbacountif excel 2007vba excel passwordmicrosoft office project management templatesnested vlookuphow to change excel columns to rowsuseful vba macrosselect sheet excel vbaexcel vba msgbox inputisna excel formulasave excel to xmlexcel vba formattingblank excel worksheetloop formula in excelexcel 2010 macro recorderworksheet unprotectproject estimation template excelexcel macro activesheetvba excel unmerge cellsbest excel dashboard examples