How to Delete Row Using VBA?

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.

How to Delete Row Using VBA: 14 Ways

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 and Prices.

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

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.

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 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.

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.

Related Content: How to Delete Rows in Excel with Specific Text

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 cells.

Press F5

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

Related Content: How to Remove Highlighted Rows in Excel

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 the 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, 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.

Related Content: How to Delete Hidden 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.

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.

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.

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.

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.

12. Deleting Row Based on the 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.

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.

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.

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 data 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.

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.

Download Workbook

VBA Delete Row.xlsm

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.

Get FREE Advanced Excel Exercises with Solutions!

How to Delete Row Using VBA?

How to Delete Row Using VBA: 14 Ways

1. Delete a Single Row Using VBA

2. Delete Multiple Rows Using VBA

3. Deleting a Row by Selecting

4. Deleting All Rows in the Selection

5. Deleting a Row If Any Cell is Empty

6. Deleting a Row If the Entire Row is Empty

7. Deleting Every nth Row

8. Deleting Row Based On Cell Value

9. Deleting Duplicate Rows

10. Deleting Rows in a Table

11. Deleting Visible Rows After Filtering

12. Deleting Row Based on the Last Active Cell

13. Deleting Rows having any Strings

14. Deleting Row Based on Dates

Practice Section

Conclusion

Related Articles

Leave a reply Cancel reply