How to Delete Row Using VBA?

Get FREE Advanced Excel Exercises with Solutions!

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.

vba delete row

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

vba delete row

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 version according to your convenience.

Read More: How to Delete Rows Using Excel Shortcuts


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 Multiple Rows in Excel at Once 


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 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 Selected Rows in Excel


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

Read More: Delete All Rows Below a Certain Row in Excel 


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

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.

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

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

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.

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

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

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.

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


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


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


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


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


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.

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


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


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

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


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.

practice


Download Workbook


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo