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.

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.