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.
50+ Project Management Templates Pack
Excel PowerPoint Word
Advanced Project Plan & Portfolio Template
Business Presentations Templates Pack
20+ Excel Project Management Pack
20+ PowerPoint Project Management Pack
10+ MS Word Project Management Pack
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.
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.
‘Specifying the Rows to delete and Deleting the Rows using EntireRow.Delete method.
‘Ending the sub procedure to delete entire row.
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.
‘First row is deleting
‘Now Second row becomes the first row- again we are deleting the first row.
‘Now Third row becomes the first row- again delete command is deleting the first row.
‘We have deleted the first 3 rows, and ending the sub procedure here.
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.
‘Declaring the variable iCntr to store the for loop iterations.
‘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.
‘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.
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.
‘Declaring the variable store the last row number and use it as counter variable.
‘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
iCntr = iCntr – 1
‘ending the sub procedure here.
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.