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


ms access vba open formsimple cutting worksheetscombine multiple spreadsheetsvba comment codeexcel pivot table referenceexcel interview questions and answers pdfexcel macro examplesfso create folderrange properties vbadefination of vlookupvba byvalproject management excel spreadsheetexcel 2003 gantt chart templateexcel vba last rowdo until loop vba3d graph in excelhow do you insert a row in excelexcel macro exampleshyperlink macrosamples of meeting minutes templateexcel how to sort alphabeticallydeveloper ribbon in excel 2007eliminate duplicates in excel 2010planner excel templatedata definition language commandsi forgot my excel sheet protection passwordchoose function in excelhow to delete blank cells in excel 2010insert several rows in excelexcel short cut keyvba msgbox inputhow to unprotect excelprograming excelexcel templates databaseexcel saveas fileformatdelete extra rows in excelscheduling in excel templaterecordset sqlexample of countifapplication left vbavba select sheetunhide hidden columns in excelvlookup excel exampleshow to connect excel to sqltranspose excel vbaprotect excel passwordrow vbaunmerging cells in excelif statements on excelmicro in excel 2007vba excel objectexcel transfer data to another sheetexcel vba powerpointvba sql whereunprotect excel 2010 without passwordexcel copy macroexcel macro open worksheetscrum call formathow to use iferror in excel 2010activate vbahow to remove blanks in excelsum vlookuparray lookup excelcheckbox in powerpointexcel combine data from multiple sheets into onetelephonic interview questions and answers pdfmicrosoft excel chart tutorialvba subdefine vbavba combobox valueselection vbarows vbaremove duplicates excel 2010dashboard examples pptexcel formula templatesweekly calendar template 2014 excellearn macros