VBA Delete Rows

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will talk about and show how to use VBA to delete a row or rows in Excel. We will go through some easy techniques to do the task smoothly.

The use of VBA to Remove rows in Excel is beneficial for data automation & customization, error reduction, and for efficiency & flexibility.

So let’s dive into the following article to explore this topic.


Download Practice Workbook


Delete a Single Row

In this section, we will delete a single row using VBA.

  • Type the following VBA code in the Module.
Sub Deleting_a_Single_row()
Rows(6).EntireRow.Delete
End Sub

Code for Deleting Single Row

  • After that, when we Run the code, Row6 will be deleted.

Result after deleting a single row


Delete Multiple Rows

In this section, we will delete multiple rows.

  • Type the following VBA code in the Module.
Sub Delete_Multiple_rows()
Range("B5:B7").EntireRow.Delete
End Sub

Code for Deleting Multiple Rows

  • After that, when we Run the code, Rows will be deleted.

Result after deleting multiple rows


Delete Selected Rows

Here, we will delete selected rows.

  • Type the following VBA code in the Module.
Sub Delete_Selected_rows()
Selection.EntireRow.Delete
End Sub

VBA Code to delete selected Rows

  • Then, select the rows you want to delete >> Run the code.

Selected Rows got Deleted


Delete Alternate Rows

Here, we will delete alternate rows.

  • Type the following VBA code in the Module.
Sub Delete_Alternate_Rows()
RCount = Selection.Rows.Count
For i_row = RCount To 1 Step -2
Selection.Rows(i_row).E ntireRow.Delete
Next i_row
End Sub

VBA Code to Delete Alternate Rows

  • Afterwards, select the range of cells >> Run the code.

Alternate Rows got Deleted


Delete Empty Rows

In this section, we will delete empty rows.

  • Type the following VBA code in the Module.
Sub Delete_Empty_Rows()
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

VBA Code to Delete Empty Rows

  • After that, select the range of rows >> Run the code.

Empty Rows got Deleted


Delete Rows Based on Specific Cell Content

In this section, we will delete empty rows.

  • Type the following VBA code in the Module.
Sub delete_Rows_Based_on_Cell()
Set my_row = Selection
my_row.EntireRow.Delete
End Sub

VBA Code to Delete Rows Based on Cell

  • After that, select the range of rows >> Run the code.

Deleted Rows based on Cell


Delete Last Row

In this section, we will delete the last row.

  • Type the following code in the Module.
Sub Delete_Last_Row()
Cells(Rows.Count, 2).End(xlUp).EntireRow.Delete
End Sub

Delete Last Row

  • Now, Run the code and the last row of the dataset will be deleted.

Last Row got Deleted


Delete Duplicate Rows

Here, we will delete duplicate rows.

  • Type the following code in the Module.
Sub Remove_Duplicate_Rows()
Range("B4:D13").RemoveDuplicates Columns:=2
End Sub

VBA Code to Delete Duplicate Rows

  • Next, Run the code and duplicate rows of the dataset will be deleted.

Duplicate Rows got Deleted


Delete Rows Based on Specific Cell Content

Here, we will delete rows based on specific cell content.

  • Type the following code in the Module.

Sub delete_rows_based_on_specific_text()

Dim i_cell As Integer
Set my_rng = Worksheets("Specific Cell Content").Range("B4:D11")
For i_cell = my_rng.Rows.Count To 1 Step -1
If my_rng.Cells(i_cell, 2).Value = "Engineer" Then
my_rng.Cells(i_cell, 2).EntireRow.Delete
End If
Next i_cell
End Sub

VBA Code to delete rows based on specific text

  • After that, Run the code to delete rows based on specific cell content.

Rows got deleted based on specific cell content


Delete Filtered Rows

In this section, we will delete filtered rows.

  • Type the following code in the Module.
Sub Delete_Filtered_Rows()
Range("B5:D11").SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub

VBA code to delete filtered rows

  • Next, Run the code to delete filtered rows.

Filtered Rows got deleted


Delete Row in Excel Table

Here, we will delete row from a Table.

  • Type the following code in the Module.
Sub delete_row_from_table()
Worksheets("Delete Row from table").ListObjects("Table1").ListRows(5).Delete
End Sub

VBA code to delete rows from table

  • Now, Run the code to delete a row from the Table.

Row got deleted from Table


VBA Delete Rows: Knowledge Hub


Conclusion

This article discusses the way we can use VBA to delete a row or rows in Excel. This article extensively represents the topic, and we believe you thoroughly understand the topic.

Here, we present how you can delete a single row, multiple rows, selected rows, alternate rows, empty rows, rows based on cell content, the last row of a dataset, based on specific cell content, filtered rows, and rows from Excel table.

Thank you for reading this article. We hope you find this article beneficial. If you have any queries or suggestions, please let us know in the comment section.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo