Whenever working with Excel, one recurring activity you might have to complete is deleting an entire row from your worksheet. You can do it manually. But if you have a big dataset then deleting rows manually will be time-consuming. But with VBA you can write a simple code that will allow you to delete the entire row in Excel. You can also delete multiple rows at a time by using VBA. The focus of this article is to use Excel VBA to delete entire row.
In this video, you can see that I have deleted an entire row from my Excel sheet by running a simple VBA code. This is the basic code for deleting a row. You will be able to know about other processes in this article.
Download Practice Workbook
You can download the practice workbook from here.
How to Launch VBA Editor in Excel
The first thing you need to know while working with VBA is how to launch the VBA editor. Let me show you the steps of launching the VBA editor in Excel.
- To begin with, go to the Developer tab >> select Visual Basic.
- Now, the Visual Basic Editor will open >> select Insert tab >> select Module.
- Finally, a Module will open. You can write your VBA code here.
Note:
If you don’t have the Developer tab on your Ribbon then you can add it easily by following these steps.
- Go to the File
- Select Options.
- Excel Options dialog box will open >> Select Customize Ribbon.
- Select Developer from the Main Tabs.
- Then, select OK and the Developer tab will be added to the Ribbon.
Excel VBA to Delete Entire Row (13 Suitable Examples)
In this section, I will show you 13 different examples. You will be able to delete the entire row by following these examples. These examples will cover different scenarios. Let’s explore the examples.
1. Deleting an Entire Row Using VBA in Excel
In this example, I have deleted the 8th row of the sheet. I used the Rows property to specify the row number I want to delete. Let’s see how you can delete an entire row by mentioning the row number in Excel VBA.
- Open a Module in VBA Editor and write the following code.
Sub Deleting_entire_row()
'used Worksheet.Rows property to specify the row no _
and Delete method to delete the row
Worksheets("Deleting Entire Row").Rows(8).Delete
End SubHere, I used the Worksheet.Rows property to specify the row that I want to delete. I also used the Delete method to delete the specified row.
- Now, Save the VBA code.
- Finally, Run the VBA code to delete the entire 8th row.
2. Specifying Range to Delete Entire Row
Here, I just specified a single cell of a row by using the Range object. And then deleted the entire row that contains that specified cell. Let me show you how you can delete the entire row from a single cell range.
- To begin with, open a Module and write the following code.
- Save the code.
Sub specifying_range_to_delete_row()
'Used Range.EntireRow property to specify row no 11 _
and Delete method to delete the row
Worksheets("Specifying Range to Delete Row").Range("B11").EntireRow.Delete
End Sub🔎 How Does the Code Work?
Worksheets("Specifying Range to Delete Row").Range("B11").EntireRow.Delete- Here, I used the Range object to specify Cell B11.
- Then, used the EntireRow property to select the entire row of the specified range which is row 11 in this example.
- Finally, used the Delete method to delete the row.
- Now, Run the code to delete the entire row.
3. Deleting Entire Row Based on Selecting a Cell
In this example, I used the Selection property. You can select a cell and delete the entire row by just running the code. Here, you can also select multiple cells to delete their entire rows.
- Write the following code in a VBA Module.
- Save the code.
Sub selecting_range_to_delete_row()
'Used Selection property to set my_rng according _
to selection
Set my_rng = Selection
my_rng.EntireRow.Delete
End SubHere, I used the Selection property to set my_rng. The rest of the code is like the previous code.
- Now, select a cell from the row you want to delete and Run the code to delete the entire row of the selected cell.
4. Removing Multiple Rows in Excel
You will be able to delete multiple rows by using the Rows property by following this example. You will have to mention the row numbers you want to delete. Let’s see how you can delete multiple rows using VBA in Excel.
- Open a VBA Module and write the following code.
- Then, Save the VBA code.
Sub Removing_multiple_rows()
'Used Worksheet.Rows property to specify rows 8 to 10 _
and Delete method to delete these rows
Worksheets("Removing Multiple Rows").Rows("8:10").Delete
End SubHere, I followed the same procedure as the 1st example to delete the rows. I just mentioned the row numbers that I want to delete in the Rows property.
- Finally, Run the code to delete the specified rows.
5. Deleting Multiple Rows by Specifying Range
In this example, I defined the cell range to delete their entire rows. You will be able to delete the entire rows by mentioning the cell range in the VBA code. Let’s see how you can do that.
- Write the following VBA code in a Module.
- Save the code.
Sub Specifying_range_to_delete_multiple_rows()
'Used Set Statement to set cell range B8:B10 as my_rng
Set my_rng = Worksheets("Deleting Multiple Rows").Range("B8:B10")
my_rng.EntireRow.Delete
End Sub🔎 How Does the Code Work?
Set my_rng = Worksheets("Deleting Multiple Rows").Range("B8:B10")- Here, I used Set Statement to set cell range B8:B10 as my_rng.
my_rng.EntireRow.Delete- In this part, I used the EntireRow property to select the entire row of the specified range and the Delete method to delete the specified rows.
- Finally, Run the code to delete multiple rows.
6. Removing Blank Rows in Excel
Here, you can see that I have deleted the blank rows from the dataset. You will be able to delete the rows that are fully blank by following this example. If the row contains a single cell with data it will not be able to delete that row. Let’s see how you can delete the entire row if it is fully blank in VBA Excel.
- Open a Module and write the following code.
- Save the VBA code.
Sub removing_blank_rows()
'used Set Statement to set cell range B4:F16 as my_rng
Set my_rng = Worksheets("Removing Blank Rows").Range("B4:F16")
'used For Each Loop to check each cell of my_rng
For Each my_cell In my_rng
'used If Statement to check if entire row of the cell is empty
If Application.WorksheetFunction.CountA(my_cell.EntireRow) = 0 Then
my_cell.EntireRow.Delete
End If
Next my_cell
End Sub🔎 How Does the Code Work?
For Each my_cell In my_rng
'used If Statement to check if entire row of the cell is empty
If Application.WorksheetFunction.CountA(my_cell.EntireRow) = 0 Then
my_cell.EntireRow.Delete
End If
Next my_cell- Here, I used the For Each Loop to check every cell in my_rng.
- Then, I used the If Statement to check if the cell was blank. In the If Statement, I used the WorksheetFunction property to use the CountA function to check the blank cells.
- Finally, Run the code to delete the blank rows.
7. Deleting an Entire Row If It Contains Blank Cell Using VBA
In this example, you can see that I have deleted the entire row if it contains any blank cells. Let me show you how you can delete the entire row if it contains a blank cell in Excel VBA.
- To begin with, write the following code in a VBA Module.
- Then, Save the VBA code.
Sub deleting_rows_with_blank_cells()
Set my_rng = Worksheets("Deleting Rows with Blank Cells").Range("B4:F14")
'used SpecialCells method to find the blank cells
my_rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub🔎 How Does the Code Work?
my_rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete- Here, I used the SpecialCells method to find the blank cells. Then, I used the EntireRow property to select the entire row of the blank cell and the Delete method to delete the entire row.
- Now, Run the code to delete rows that contain blank cells.
8. Removing Duplicate Rows in Excel VBA
Here, you can see that my dataset contains some duplicate rows. I deleted the duplicate rows using a VBA code. Let’s see how you can do that.
- Open a VBA Module and write the following code.
- Then, Save the code.
Sub removing_duplicate_rows()
Set my_rng = Worksheets("Removing Duplicate Rows").Range("B4:F16")
'used RemoveDuplicates method to remove duplicate rows from my_rng
my_rng.RemoveDuplicates Columns:=1
End Sub🔎 How Does the Code Work?
my_rng.RemoveDuplicates Columns:=1- Here, I used the RemoveDuplicates method to remove the duplicate rows. I checked column 1 of my_rng to find duplicates.
- Now, Run the VBA code to remove the duplicate rows.
9. Deleting Rows From Table in Excel
In this example, I will show you how you can delete an entire row from a table in Excel using VBA. In the video, you can see that I have deleted an entire row from the table. If you have a table from which you want to delete rows then follow this example to do that. Let’s see how you can do that.
- Write the following VBA code in a Module and Save it.
Sub deleting_rows_from_table()
'Used ListObjects object to select Table1 and ListRows object to define the row no
Worksheets("Deleting Rows from Table").ListObjects("Table1").ListRows(5).Delete
End Sub🔎 How Does the Code Work?
Worksheets("Deleting Rows from Table").ListObjects("Table1").ListRows(5).Delete- Here, I used the ListObjects object to select Table1 which is the name of the table I inserted.
- Then, I used the ListRows object to specify the row number of the table that I wanted to delete.
- Run the VBA code to delete rows from a table.
Read More: Excel VBA to Delete Table Row (8 Examples)
10. Removing Filtered Rows in Excel Using VBA
In this example, I will show you how you can delete the filtered rows using VBA. in the video, you can see I have filtered the rows that I want to delete from the dataset. And then used the VBA code to delete the filtered rows. Let’s see how to do that.
- Open a VBA Module and write the following code.
- Save the VBA code.
Sub removing_filtered_rows()
Set my_rng = Worksheets("Removing Filtered Rows").Range("B5:F14")
'used SpecialCells method to find the visible cells after filtering
my_rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub🔎 How Does the Code Work?
my_rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete- Here, I used the SpecialCells method to find the cells that are visible after filtering.
- Then, I used the EntireRow property to select the entire row of the visible cell and the Delete method to delete the entire row.
- Run the VBA code to delete the filtered rows.
11. Deleting Last Row That Contains Data
If you want to delete the last row that contains data then this example will be helpful for you. You can see that I have deleted the last row of my dataset here. Let me show you how to delete the entire last row of a dataset in Excel using VBA.
- In the beginning, open a Module in Visual Basic Editor and write the following code in it.
- Save the code.
Sub deleting_last_row()
'used Rows.Count property to select last cell of column 2 _
and Range.End property to select the last cell with data
Worksheets("Deleting Last Row").Cells(Rows.Count, 2).End(xlUp).EntireRow.Delete
End Sub🔎 How Does the Code Work?
Worksheets("Deleting Last Row").Cells(Rows.Count, 2).End(xlUp).EntireRow.Delete- In this part, I used the Count property in the Cells property to get the last cell of column 2.
- Then, I used the End property to find the last cell with data from column 2.
- Finally, used the Delete method to delete the last row with data.
- Now, Run the VBA code to delete the last row.
12. Removing Alternative Rows From Excel Sheet
In this example, I have deleted the alternative rows of the dataset. I wanted a list of employees who have odd Employee IDs. So, I deleted the alternative rows. Let’s see how you can do that.
- Open a VBA Module and write the following code.
- Save the code.
Sub removing_alternative_rows()
Dim i As Integer
Set my_rng = Worksheets("Removing Alternative Rows").Range("B5:F14")
'used For Next Loop to check from last row to first row _
and -2 as Step to select alternative row
For i = my_rng.Rows.Count To 1 Step -2
my_rng.Rows(i).Delete
Next i
End Sub🔎 How Does the Code Work?
For i = my_rng.Rows.Count To 1 Step -2
my_rng.Rows(i).Delete
Next i- Here, I used a For Next Loop to go through the rows of my_rng. I used -2 as Step because I want the For Next Loop to go in the reverse direction and check every alternative row. The For Next Loop is used in the negative direction because the number of rows changes each time a row is deleted from the range.
- Now, Run the code to delete alternative rows.
13. Using UserForm to Delete Entire Row from Cell Reference
In this example, I used a UserForm to select the range to delete an entire row in Excel. By following this method, you will be able to delete both single and multiple rows. Let’s see how you can create this UserForm.
- Open Visual Basic Editor >> select Insert tab >> select UserForm.
- Now, change the Caption of the UserForm according to your preference from the Properties window.
- Then, select Label from ToolBox >> click and drag your mouse cursor where you want the Label.
- Now, a Label will be inserted in the UserForm.
- Change the Caption of the Label accordingly from the Properties window.
- Now, insert a RefEdit from the Toolbox in the same way.
- Then, insert a CommandButton.
- This is how the UserForm looks >> Double-click on the CommandButton.
- Then, write the following code.
Private Sub CommandButton1_Click()
'used UserForm1.RefEdit1.Value to get range from RefEdit1
Set my_rng = Range(UserForm1.RefEdit1.Value)
my_rng.EntireRow.Delete
'used Unload statement to unload UserForm
Unload UserForm1
End Sub🔎 How Does the Code Work?
Set my_rng = Range(UserForm1.RefEdit1.Value)- Here, I used the Value property to get the value of the selected range from RefEdit.
Unload UserForm1- In this part, I used the Unload statement to unload the UserForm after deleting the rows.
- Now, go back to the Worksheet.
- Go to the Developer tab >> select Insert >> select CommandButton from ActiveX Controls.
- Click and drag your mouse cursor where you want the CommandButton.
- Now, you will see that you have inserted the CommandButton.
- Right-click on the CommandButton >> select Properties.
- Change the Caption according to your preference. I used Get UserForm as the Caption.
- Select Font >> Click on the marked button to change the Font of the Caption.
- Change Font, Font Style, and Size according to your preference.
- Then, select OK.
- Right-click on the CommandButton >> select View Code.
- Now, write the following code.
Private Sub CommandButton1_Click()
Load UserForm1
UserForm1.Show
End Sub🔎 How Does the Code Work?
Load UserForm1- Here, I used the Load statement to load the UserForm.
UserForm1.Show- In this part, I used the Show method to display the UserForm.
- Finally, click on the CommandButton to load the UserForm and follow this video to delete the entire row.
How to Delete Entire Row in Excel VBA If Cell Contains Certain Text
In this section, I will show you how you can delete the entire row if it contains a certain text in Excel VBA. Suppose you have a list and you want to delete the rows that contain a certain text. Let’s explore the examples to know how you can do that.
1. Deleting Entire Row If Cell Contains Certain Text
In this example, you can see that I have deleted the rows that contain information about the employee from the HR department. In the same way, you can delete the rows that contain certain text using VBA in Excel. Let’s see how to do that.
- Open a VBA Module and write the following code.
Sub deleting_rows_based_on_certain_text()
Dim i As Integer
Set my_rng = Worksheets("Deleting Row Based on Text").Range("B4:F14")
'used For Next Loop to check from last row to first row _
and -1 as Step to select in reverse direction
For i = my_rng.Rows.Count To 1 Step -1
If my_rng.Cells(i, 5).Value = "HR" Then
my_rng.Cells(i, 5).EntireRow.Delete
End If
Next i
End Sub🔎 How Does the Code Work?
For i = my_rng.Rows.Count To 1 Step -1
If my_rng.Cells(i, 5).Value = "HR" Then
my_rng.Cells(i, 5).EntireRow.Delete
End If
Next i- Here, I used a For Next Loop to go through the rows of my_rng. I used -1 as Step because I want the For Next Loop to go in the reverse direction. The For Next Loop is used in the reverse direction because the number of rows changes each time a row is deleted from the range.
- Then, I used the If Statement to check if the cell value matches the specified text.
- Finally, Run the code to delete the rows with specified text.
2. Removing Entire Row Based on Text Using InputBox in VBA
Here, I have used an InputBox to choose the specific text so that I can delete the rows that contain that text. This method is more dynamic than the previous one. Let me show you how you can do that.
- Write the following code in a VBA Module.
- Save the code.
Sub Using_input_box()
Dim my_val As String
Dim i As Integer
'used InputBox function to take text value
my_val = InputBox("Enter Your Text")
Set my_rng = Worksheets("Using InputBox").Range("B4:F14")
'used For Next Loop to check from last row to first row _
and -1 as Step to select in reverse direction
For i = my_rng.Rows.Count To 1 Step -1
'used If Statement to check if text matches with InputBox _
and LCase function to avoid case sensitivity
If LCase(my_rng.Cells(i, 5).Value) = LCase(my_val) Then
my_rng.Cells(i, 5).EntireRow.Delete
End If
Next i
End Sub🔎 How Does the Code Work?
my_val = InputBox("Enter Your Text")- Here, I used the InputBox function to take the text value from the user.
For i = my_rng.Rows.Count To 1 Step -1
'used If Statement to check if text matches with InputBox _
and LCase function to avoid case sensitivity
If LCase(my_rng.Cells(i, 5).Value) = LCase(my_val) Then
my_rng.Cells(i, 5).EntireRow.Delete
End If
Next i- In this part, I used the For Next Loop in the reverse direction.
- Then, I used the If Statement to check if the text in the cell matches the text in InputBox. I also used the LCase function to avoid case sensitivity.
- Now, run the VBA code to delete the rows that contain the text written in the InputBox.
3. Filtering Rows Based on Cell Value and Deleting Them
In this example, I filtered the range to find out the rows that contain specific text and then deleted the visible rows. Let’s see how you can do that.
- Open a Module in the Visual Basic Editor and write the following code.
- Then, Save the VBA code.
Sub filtering_and_deleting_rows()
'used Application.DisplayAlerts property to suppress alert messages
Application.DisplayAlerts = False
Set my_rng = Worksheets("Filtering and Deleting Rows").Range("B4:F14")
'used Range.AutoFilter method to filter my_rng
my_rng.AutoFilter Field:=5, Criteria1:="HR"
Worksheets("Filtering and Deleting Rows").AutoFilter. _
Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete
End Sub🔎 How Does the Code Work?
Application.DisplayAlerts = False- Here, I used the DisplayAlerts property to suppress alert messages.
my_rng.AutoFilter Field:=5, Criteria1:="HR"- I used the AutoFilter method to filter my_rng based on the mentioned criteria and mentioned field.
Worksheets("Filtering and Deleting Rows").AutoFilter. _
Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete- Here, I used the SpecialCells method to find and delete the visible rows after filtering. I used the Offset property to start deleting after the 1st row that contains the headers.
- Run the VBA code to delete the entire row if it contains certain text.
Read More: Excel VBA to Delete Row Based on Cell Value
How to Delete Rows Based on Numeric Condition in Excel
In this example, I wanted a list of the employees who have even Employee ID. So, I deleted the rows that contained odd Employee ID. Let me show you how you can delete the entire row in Excel VBA based on numeric conditions like this one.
- Open a VBA Module and write the following code.
- Save the code.
Sub Deleting_rows_based_on_numeric_condition()
Dim i As Integer
Set my_rng = Worksheets("Numeric Condition").Range("B5:F14")
For i = my_rng.Rows.Count To 1 Step -1
'used Mod operator to find if cell value is odd
If my_rng.Cells(i, 1).Value Mod 2 <> 0 Then
my_rng.Cells(i, 1).EntireRow.Delete
End If
Next i
End Sub🔎 How Does the Code Work?
For i = my_rng.Rows.Count To 1 Step -1
'used Mod operator to find if cell value is odd
If my_rng.Cells(i, 1).Value Mod 2 <> 0 Then
my_rng.Cells(i, 1).EntireRow.Delete
End If
Next i- Here, I used the For Next Loop in the reverse direction to check every cell from column 1 in my_rng.
- Then, I used an If Statement to check if the cell value was odd. I used the Mod operator for that.
- Run the VBA code to delete rows that match the numeric condition.
How to Delete Entire Column Using Excel VBA
Here, you can see that I have deleted an entire column using VBA in Excel. The purpose of this section is to show you how you can delete an entire column. Let’s see the steps.
- Open a VBA Module and write the following code in it.
- Save the VBA code.
Sub deleting_entire_column()
'used Columns property to select column "E" _
and Delete method to delete column "E"
Worksheets("Deleting Entire Column").Columns("E").Delete
End SubHere, I used the Columns property to select which column to delete and then the delete method to delete the column.
- Now, Run the VBA code to delete the column.
Things to Remember
Whenever working with VBA, you must save your Excel file as Excel Macro-Enabled Workbook. Otherwise, the macros will not work.
Conclusion
So, you have reached the end of my article. Here, I tried to explain how to use Excel VBA to delete entire row. You can see that it is a quite straightforward process which you can accomplish by following a few easy steps. I have tried to cover everything there is to know about deleting an entire row in Excel VBA. I hope this article was clear to you. If you face any problems, please let us know through the comment section.
