How to Delete Row Using VBA (14 Ways)

If you are looking for some of the easiest ways to delete row using VBA, then you are in the right place.
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.

vba delete row

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

vba delete row

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

vba delete row

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


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

vba delete row

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

deleting single row

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

vba delete row

After that, a Module will be created.

deleting single row

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.

deleting single row

➤Press F5

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

deleting single row

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


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

vba delete row

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.

deleting multiple rows

➤Press F5

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

vba delete row

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


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

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

deleting rows with respect to 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

deleting rows with respect to active cell

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

deleting rows with respect to active cell

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

deleting rows with respect to active cell


Method-4: Deleting All Rows in the Selection

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

deleting all rows in the selection

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.

deleting all rows in 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

deleting all rows in the selection

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

deleting all rows in the selection

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

deleting all rows in the selection

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


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

deleting row with empty 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.

deleting row with empty cell

➤Press F5

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

deleting row with empty cell

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


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

deleting empty row

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.

deleting empty row

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

deleting empty row

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


Method-7: Deleting Every nth Row

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

deleting nth row

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.

deleting nth row

➤Press F5

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

deleting nth row

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


Similar Readings:


Method-8: Deleting Row Based On Cell Value

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

deleting row based on cell value

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.

deleting row based on cell value

➤Press F5

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

deleting row based on cell value

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


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

deleting duplicate rows

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.

deleting duplicate rows

➤Press F5

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

deleting duplicate rows

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


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

deleting rows in a table

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.

deleting rows in a table

➤Press F5

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

deleting rows in a table

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


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

deleting filtered rows

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.

deleting filtered rows

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

deleting filtered rows

➤Click on the Select All option
➤Press OK

deleting filtered rows

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

deleting filtered rows

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


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

last active cell

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.

last active cell

➤Press F5

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

last active cell

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


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

deleting rows with string

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.

deleting rows with string

➤Press F5

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

deleting rows with string

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


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

deleting rows with specific dates

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

deleting rows with specific dates

➤Press F5

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

deleting rows with specific dates

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 below in a sheet named Practice. Please do it by yourself.

practice


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo