How to Delete Row Using VBA (14 Cases)

Deleting rows in Excel can be done easily using Excel’s default functionalities. But sometimes, you might need to use Excel VBA to do so. Or if you’re making an Excel VBA application, and in some parts of the Code, you might need to delete some rows. So, here you will get around 14 cases of Deleting Rows in Excel using VBA.

Let’s dive into the main article.

Download Workbook

14 Ways to Delete Row Using VBA

Here, I have the three data tables to show the ways of deleting rows using VBA. The first table contains some Products of a company and their Sizes, Prices.

The second one has some Product Codes and their corresponding Product Sizes and Prices

And the last one contains some Project Names and their respective Start Dates and Costs.

For creating the article, I have used Microsoft Excel 365 version, you can use any other version according to your convenience.

1. Delete a Single Row Using VBA

Let’s say, you want to delete the row containing Product Shoe 1, and to delete this single row you can follow this method.

Step-01:
➤Go to Developer Tab>>Visual Basic Option

Then, the Visual Basic Editor will open up.
➤Go to Insert Tab>> Module Option

After that, a Module will be created.

Step-02:
➤Write the following code

Sub dltrow1()

Worksheets("Single").Rows(7).EntireRow.Delete

End Sub

Here, “Single” is the worksheet name, and Rows(7) will select the row number.
.EntireRow.Delete will delete the entire row 7.

➤Press F5

Result:
In this way, you will delete the row containing the Product name Shoe 1.

Read More: How to Delete Rows in Excel: 7 Methods

2. Delete Multiple Rows Using VBA

If you want to delete multiple rows like the rows containing the Product names Shoe1, Shoe2, and Shoe3, then you can follow this VBA code.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrow2()

Rows(13).EntireRow.Delete
Rows(10).EntireRow.Delete
Rows(7).EntireRow.Delete

End Sub

Here, row numbers 13, 10, and 7 will be deleted and in writing serially the row numbers you have to write from the last row number to the first-row number like this code.

Otherwise, after deleting the first row like Row 7 the remaining rows below this row will move one row up and thus the Row 10 will be Row 9, and Row 13 will be Row 12. For this reason, you will not be able to delete the correct rows.

➤Press F5

Result:
Then, you will delete the rows containing the Product names Shoe 1, Shoe2, and Shoe3.

Read More: How to Delete Multiple Rows in Excel (3 Methods)

3. Deleting a Row by Selecting

Here, I will show the way to delete a row with respect to an active cell of that row.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrow3()

ActiveCell.EntireRow.Delete

End Sub

It will delete the row having an active cell.

➤Save the code.

Step-02:
➤Select any cell of a row that you want to delete ( Here I have selected cell B7)
➤Go to Developer Tab>>Macros Option

After that, the Macro Wizard will open up.
➤Select the Macro name dltrow3 and press Run

Result:
Afterward, you will delete the row containing the Product name Shoe 1.

4. Deleting All Rows in the Selection

For deleting all of the rows of a selection you can follow this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrow4()

Selection.EntireRow.Delete

End Sub

It will delete all of the rows of the selection.

➤Save the code.

Step-02:
➤Select the range of rows you want to delete (Here, I have selected range B7:D9)
➤Go to Developer Tab>>Macros Option

After that, the Macro Wizard will open up.
➤Select the Macro name dltrow4 and press Run

Result:
Then, you will delete the rows containing the Product names Shoe 1, Jacket1, and Jacket2.

Read More: How to Delete Selected Rows in Excel(8 Approaches)

5. Deleting a Row If Any Cell is Empty

Here, I have an empty cell which is B9 (I have removed the value from this cell for this method), and using a VBA code I will delete the row having B9 cell.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrow5()

Range("B5:D13").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

It will delete all of the rows in the range “B5:D13” having any blank cell.

➤Press F5

Result:
Then, you will be able to delete the row containing the blank cell.

Read More: How to Remove Empty Rows in Excel (11 Methods)

6. Deleting a Row If the Entire Row is Empty

Here, I have an empty cell which is B9 (I have removed the values for this method), and an empty row which is Row 12 (I have removed the values for this method), and using a VBA code and the COUNTA function I will delete the row which is empty.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrow6()

Dim cell As Range

For Each cell In Range("B5:D13")

    If Application.WorksheetFunction.CountA(cell.EntireRow) = 0 Then
    cell.EntireRow.Delete
    End If

Next cell

End Sub

“B5:D13” is the data range and FOR loop will work for every cell in this range.

CountA(cell.EntireRow) will return the number of non-blank cells and when it becomes 0 then the row will be deleted.

➤Press F5

Result:
After that, you will be able to delete the empty row but it will not delete any row not having all of the cells of this row empty.

Read More: How to Use VBA to Delete Empty Rows in Excel

7. Deleting Every nth Row

You can delete every nth row (in this case every 3rd row) by following this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrow7()

rc = Range("B5:D13").Rows.Count

For i = rc To 1 Step -3
Range("B5:D13").Rows(i).EntireRow.Delete
Next i

End Sub

“B5:D13” is the data range and rc will return the total row number of this range which is 9.
Here, the FOR loop will start from the last row which is rc or 9 in this case and ends with the first row.
Step -3 will help to delete every 3rd row in this range.

➤Press F5

Result:
Then, you will delete the rows containing the Product names Shoe 1, Shoe2, and Shoe3.

Read More: How to Delete Every nth Row in Excel (Easiest 6 Ways)

Similar Readings

• How to Delete Selected Rows with Excel VBA (A Step-by-Step Guideline)
• Delete Unused Rows in Excel (8 Easy Ways)
• How to Delete Hidden Rows in Excel (3 Methods)
• Delete Infinite Rows in Excel (5 Easy Ways)
• How to Delete Rows in Excel That Go on Forever (4 Easy Ways)

8. Deleting Row Based On Cell Value

In this method, I will delete the row having a specific value Shirt 2.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrow8()

Dim cell As Range

For Each cell In Range("B5:D13")

If cell.Value = "Shirt 2" Then
    cell.EntireRow.Delete
End If

Next cell

End Sub

“B5:D13” is the data range and the FOR loop will work for every cell in this range.
If any cell of a row contains the value “Shirt 2” then the row will be deleted.

➤Press F5

Result:
In this way, you will delete the row containing the Product name Shirt 2.

Read More: VBA Macro to Delete Row if Cell Contains Value in Excel (2 Methods)

9. Deleting Duplicate Rows

Here, I have two rows having a Product Code 97375 and so I want to delete one of these rows which are marked by a red box below.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrow9()

Range("B5:D13").RemoveDuplicates Columns:=1

End Sub

Here, “B5:D13” is the data range and Columns:=1 means it will search for duplicate values only in one column which is the starting column Column B but if you want to search in other columns too you can use this number according to your needs.

➤Press F5

Result:
Afterward, you will be able to delete the row which is similar to another row.

Read More: How to Delete Duplicate Rows in Excel with VBA (8 Effective Ways)

10. Deleting Rows in a Table

Suppose, you have the following table whose name is Table1 and now you want to delete  Row number 6 of this table. To do this you can follow this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrow10()

ActiveWorkbook.Worksheets("Table").ListObjects("Table1").ListRows(6).Delete

End Sub

Here, “Table” is the sheet name, “Table1” is the table name and 6 is the row number of this table that you want to delete.

➤Press F5

Result:
In this way, you will delete your desired row of this table.

Read More: How to Remove Duplicate Rows Based on One Column Using Excel VBA

11. Deleting Visible Rows After Filtering

Here, I have a data table that is filtered by the Price column for values more than $1,500.00 and I want to delete all of the visible rows after filtering.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrow11()

Range("B5:D13").SpecialCells(xlCellTypeVisible).EntireRow.Delete

End Sub

Here, “B5:D13” is the data range.

➤Press F5

Now, all of the visible rows after filtering will be deleted. You can now bring back the hidden rows by selecting the Filter sign in the Price column.

➤Click on the Select All option
➤Press OK

Result:
Finally, you will get the hidden rows back in the data table.

Read More: How to Filter and Delete Rows with VBA in Excel (2 Methods)

12. Deleting Row Based on Last Active Cell

Here, my last active cell is cell B13 and I want to delete this having the last active cell of the following data table.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrow12()

Cells(Rows.Count, 2).End(xlUp).EntireRow.Delete

End Sub

Here, 2 means Column B on which I have my active cell, you can change the number according to your needs.

➤Press F5

Result:
In this way, you will delete your last active row.

Read More: Excel VBA Code to Delete Rows Based on Multiple Cell Value (3 Criteria)

13. Deleting Rows having any Strings

Assume, you want to delete the rows having any string, and to do this you can follow this method.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrow13()

Dim FirstRow As Long
Dim LastRow As Long
Dim FirstColumn As Long
Dim LastColumn As Long
Dim sheet As Worksheet
Dim Rng As Range

FirstRow = 5
FirstColumn = 2
Set sheet = Worksheets("String")

 With sheet

    With .Cells

    LastRow = .Find(What:="*", LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastColumn = .Find(What:="*", LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    End With

    Set Rng = .Range(.Cells(FirstRow, FirstColumn), .Cells(LastRow, LastColumn))

 End With

 On Error Resume Next

 Rng.SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete

End Sub

Here, “string” is the sheet name, the first-row number of this data range is 5 and the starting column number is 2

The With statement lets you specify an object or user-defined type once for an entire series of statements.
On Error Resume Next statement specifies that, when a run-time error occurs, control goes to the statement following the statement where the error occurs.

LastRow and LastColumn return the last row and column of the data range.
SpecialCells(xlCellTypeConstants, xlTextValues) will select the rows having any text values in the range.

➤Press F5

Result:
Afterward, you will delete the rows having any text string.

Read More: Excel Shortcut to Delete Rows (With Bonus Techniques)

14. Deleting Row Based on Dates

Here, I will delete the rows having a special date 11/12/2021 (mm/dd/yyyy) in this method using the DATEVALUE function.

Step-01:
➤Follow Step-01 of Method-1
➤Type the following code

Sub dltrow14()

Dim FirstRow As Long
Dim LastRow As Long
Dim CriteriaColumn As Long
Dim myDate As Date
Dim sheet As Worksheet
Dim i As Long
Dim myRowsWithDate As Range

FirstRow = 5
CriteriaColumn = 3
myDate = DateValue("11/12/2021")
Set sheet = Worksheets("Date")

With sheet

    LastRow = .Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    For i = LastRow To FirstRow Step -1

        With .Cells(i, CriteriaColumn)

            If .Value = myDate Then

                If Not myRowsWithDate Is Nothing Then

                Set myRowsWithDate = Union(myRowsWithDate, .Cells)

                Else

                Set myRowsWithDate = .Cells

                End If

            End If

        End With

        Next i

    End With

    If Not myRowsWithDate Is Nothing Then myRowsWithDate.EntireRow.Delete

End Sub

Here, “Date” is the sheet name, the first-row number of this data range is 5 and the criteria column (which column has dates) number is 3.

The With statement lets you specify an object or user-defined type once for an entire series of statements.
On Error Resume Next statement specifies that, when a run-time error occurs, control goes to the statement following the statement where the error occurs.

LastRow returns the last row of the data range and here, the FOR loop will start from the last row which is LastRow or 6 in this case and ends with the first row.
DATEVALUE will convert the text date into value.

Here, Union will return the union of multiple ranges corresponding to the cells having the date 11/12/2021, and finally, the ranges will be deleted.

➤Press F5

Result:
After that, you will delete the rows having a certain date of 11/12/2021.

Related Content: Excel VBA to Delete Rows with Specific Data (9 Examples)

Practice Section

For doing practice by yourself we have provided a Practice section like the one below in a sheet named Practice. Please do it by yourself.

Conclusion

In this article, I tried to cover the easiest ways to use VBA effectively for deleting rows. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• How to Delete Rows Based on Another List in Excel (5 Methods)
• VBA to Delete Every Other Row in Excel (6 Criteria)
• How to Delete Hidden Rows in Excel VBA (A Detailed Analysis)
• Delete Rows in Excel without Affecting Formulas (2 Quick Ways)
• How to Delete Rows in Excel with Specific Text (3 Methods)