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.

Excel VBA Code Example Macro to Delete Entire Row

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.

120+ Professional Project Management Templates!
Save Up to 85% LIMITED TIME OFFER

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Browse All Templates
Excel VBA Project Management Templates

All-in-One Pack
120+ Project Management
Premium Templates
View Details

Essential Pack
50+ Project Management
Premium Templates
View Details
50+ Excel
Project Management
Templates Pack
View Details
50+ PowerPoint
Project Management
Templates Pack
View Details
25+ MS Word
Project Management
Templates Pack
View Details
Ultimate Project Management Template
View Details
Ultimate Resource Management Template
View Details
Project Portfolio Management Templates
View Details
By Last Updated: June 17, 2022Categories: VBATags:

Share This Story, Choose Your Platform!

5 Comments

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

    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.

  2. Bram Vreugdenhil August 18, 2016 at 1:37 PM

    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

  3. Subhash June 24, 2017 at 1:59 PM

    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

  4. shahzad July 6, 2019 at 12:41 AM

    Hi,

    I have on query as I have data in spread sheet where some rows need to delete on condition bases, such as example-

    ID Name error Team Contested Status
    12 ss Y XYZ FALSE
    13 LL Y XYZ TRUE
    14 KK N XYZ FALSE
    15 NN Y XYZ FALSE
    16 BB Y XYZ TRUE
    17 XX N XYZ FALSE
    18 AA Y XYZ FALSE

    Suppose I have this table where I nee to delete only those rows where cell value are TRUE without deleting header (contested)
    In contested column where two value first False and the second True so I want that true value’s row automatically delete by macro.
    Please suggest and provide solution.

  5. SHAHZAD July 6, 2019 at 12:43 AM

    Hi,

    I have on query as I have data in spread sheet where some rows need to delete on condition bases, such as example-

    ID Name error Team Contested Status
    12 ss Y XYZ FALSE

    Suppose I have this table where I nee to delete only those rows where cell value are TRUE without deleting header (contested)
    In contested column where two value first False and the second True so I want that true value’s row automatically delete by macro.
    Please suggest and provide solution.

Leave A Comment