Rifat Hassan
In this article, I’ll show you how you can delete rows in a range with VBA in Excel. You’ll learn to use VBA to delete every nth row, rows with a specific condition, and rows with a fixed text. The following gif gives an overview of the outcome that we are anticipating from the VBA codes that we will use here.
Click on the image for a better view.
How to Delete Rows in a Range with VBA in Excel (Quick View)
Here, we have the VBA command to delete an entire row. We will customize this command to delete rows of a specific range. In this instance, it deletes the entire row of cell C2 (Row 2, Column 3) of the worksheet.
Cells(2,3).EntireRow.DeleteRead More: How to Delete Rows Using Excel Shortcuts
How to Delete Rows in a Range with VBA in Excel : 3 Easy Ways
In this article, we will discuss 3 ways to delete rows in a range by using VBA codes. Here we’ve got a data set with the Names, Book Types, and Prices of some books from a bookshop called Martin Bookstore. Today, our objective is to delete rows from this data set with VBA code following different conditions.
Read More: How to Delete Multiple Rows in Excel at Once
Similar Readings:
- How to Delete Filtered Rows in Excel
- How to Delete Unused Rows in Excel
- How to Find and Delete Rows in Excel
- How to Delete Every Other Row in Excel
- How to Delete Blank Rows in Excel
- How to Delete Row If Cell Contains Specific Values in Excel
1. Run a VBA Code to Delete Every nth Row in a Range in Excel
You can run a VBA code to remove every nth row from your data set in Excel.
⧪ Steps:
- Secondly, go to the Insert tab in the VBA window.
- From the options available, select Module.
- As a result, a new module window called “Module 1” will open.
- Insert the following VBA code in the module.
Code:
Sub Delete_Every_nth_Row()
On Error GoTo Message
Dim Count As Integer
Count = 0
n = Int(InputBox("Enter the Value of n: "))
For i = n To Selection.Rows.Count Step n
Selection.Cells(i - Count, 1).EntireRow.Delete
Count = Count + 1
Next i
Exit Sub
Message:
MsgBox ("Please Enter a Valid Integer.")
End Sub⧪ Note: This code produces a Macro called Delete_Every_nth_Row.
- Thereafter, save the workbook as an Excel Macro-Enabled Workbook.
- After that, return to your worksheet and select the range of cells from where you want to delete every nth row.
- Here, I have selected my data set B4:C13. I want to delete every 3rd row of this data set. So, for the sake of visualizing, I’ve marked every 3rd row with light brown.
- Then, press ALT+F8 on your keyboard.
- As a result, a dialogue box called Macro will open. Select Delete_Every_nth_Row (The name of the Macro) and click on Run.
- Consequently, an Input Box will appear asking you to enter the value of n.
- For the sake of this example, I have entered it as 3 (As I want to remove every 3rd row.)
- Then, click OK.
- Finally, you will find every nth row (3rd in this example) deleted automatically.
Read More: How to Delete Selected Rows in Excel
Similar Readings:
- How to Delete Row If Cell Is Blank in Excel
- How to Delete Empty Rows at Bottom in Excel
- How to Delete Multiple Rows in Excel Using Formula
- How to Delete Multiple Rows in Excel with Condition
- How to Delete Rows in Excel without Affecting Formulas
- How to Delete Rows in Excel That Go on Forever
- How to Delete Infinite Rows in Excel
2. Insert a VBA Code to Remove Rows with a Specific Condition in a Range in Excel
You can also use a VBA code to remove rows with a specific condition from a data set in Excel. For example, let’s try to delete all the rows with prices greater than $30 from this data set.
⧪ Steps:
- To begin with, perform Steps 1-3 from section1 to open the VBA window and insert a new module.
- In Step 3, change the VBA code to this:
Code:
Sub Delete_Rows_with_Condition()
On Error GoTo Message
Column_number = Int(InputBox("Enter the Number of Column Where the Condition is Applied: "))
Dim Condition As String
Condition = Int(InputBox("Select Your Condition: " + vbNewLine + "Enter 1 for Greater than a Value." + vbNewLine + "Enter 2 for Greater than or Equal to a Value." + vbNewLine + "Enter 3 for Less than a Value." + vbNewLine + "Enter 4 for Less than or Equal to a Value." + vbNewLine + "Enter 5 for Equal to a Value." + vbNewLine + "Enter 6 for Not Equal to a Value."))
If Condition > 6 Then
GoTo Message
End If
Value = InputBox("Enter the Value: ")
If Condition <= 4 Then
Value = Int(Value)
Else
If VarType(Selection.Cells(1, Column_number)) <> 8 Then
Value = Int(Value)
End If
End If
For i = 1 To Selection.Rows.Count
If Condition = 1 Then
If Selection.Cells(i, Column_number) > Value Then
Selection.Cells(i, Column_number).EntireRow.Delete
i = i - 1
End If
ElseIf Condition = 2 Then
If Selection.Cells(i, Column_number) >= Value Then
Selection.Cells(i, Column_number).EntireRow.Delete
i = i - 1
End If
ElseIf Condition = 3 Then
If Selection.Cells(i, Column_number) < Value Then
Selection.Cells(i, Column_number).EntireRow.Delete
i = i - 1
End If
ElseIf Condition = 4 Then
If Selection.Cells(i, Column_number) <= Value Then
Selection.Cells(i, Column_number).EntireRow.Delete
i = i - 1
End If
ElseIf Condition = 5 Then
If Selection.Cells(i, Column_number) = Value Then
Selection.Cells(i, Column_number).EntireRow.Delete
i = i - 1
End If
ElseIf Condition = 6 Then
If Selection.Cells(i, Column_number) <> Value and Selection.Cells(i, Column_number)<>"" Then
Selection.Cells(i, Column_number).EntireRow.Delete
i = i - 1
End If
End If
Next i
Exit Sub
Message:
MsgBox "Please Enter a Valid Integer between 1 to 6 and a Logical Value."
End Sub⧪ Note: This code produces a Macro called Delete_Rows_with_Condition.
- After that, save the Workbook as an Excel Macro-Enabled Workbook. Then come back to your worksheet and select your data set to delete rows with conditions.
- Here, I’ve selected my data set B4:D13. As will delete the rows with prices greater than $30.00, I have marked them with light brown.
- Next, press ALT+F8 to open the Macro box. Then run the Macro Delete_Rows_with_Condition.
- As a result, you will get three Input Boxes. The 1st box will ask you to enter the number of the column where the condition lies.
In this example, it is 3 (Price).
- The 2nd Input box will ask you to enter a number between 1 to 6 for 6 different types of conditions.
As here our condition is to be greater than a value (Price greater than $30.00), we are entering 1.
- The 3rd Input Box will ask you to enter the Value.
As our condition is for the price to be greater than $30.00, we are entering it as 30.
- After providing all three inputs, click OK.
- As a result, you will get the rows that satisfy your condition (Price greater than 30 in this example) deleted from your data set.
Read More: Delete All Rows Below a Certain Row in Excel
Similar Readings:
- How to Delete All Rows Not Containing Certain Text in Excel
- How to Delete Rows Based on Another List in Excel
- How to Remove Rows Containing Identical Transactions in Excel
- [Fixed!] Not Enough Memory to Delete Rows Error in Excel
- How to Delete Row Using VBA
- How to Delete Row with VBA Macro If Cell Is Blank in Excel
- How to Delete Entire Row Based on Cell Value Using VBA in Excel
⧪ Additional Example:
You can use the same code to delete all the rows that have the Book Type “Novel”.
- In the 1st Input box, enter 2. (As the column Book Type is the 2nd column of the data set.)
- After that, enter 5 (As the condition is equal to a value.) in the next input box.
- Finally, enter Novel into the last input box.
- As a result, you will get all the rows with the Book Type “Novel” deleted.
⧪ Note: You can use the same code to delete rows with blank cells from a data set. Just keep the 3rd Input Box empty.
Related Content: How to Remove Highlighted Rows in Excel
Similar Readings:
- How to Delete Row If Cell Contains Value Using Macro in Excel
- How to Delete Row Using Macro If Cell Contains 0 in Excel
- VBA to Delete Every Other Row in Excel
- How to Delete Multiple Rows with VBA in Excel
3. Embed a VBA Code to Delete Rows with a Specific Text Value in a Range in Excel (Partial Match)
You can also use a VBA code to delete rows that contain a specific text value.
For example, let’s try to delete the rows with the text “History” in the book name.
⧪ Steps
- At the beginning, follow Steps 1-3 from section 1 to open the VBA window and insert a new module.
- In Step 3, change the VBA code to this:
Code:
Sub Delete_Rows_with_Specific_Text()
On Error GoTo Message
Column_number = Int(InputBox("Enter the Number of the Column Where the Text Lies: "))
Text = InputBox("Enter the Specific Text: ")
Dim Count As Integer
For i = 1 To Selection.Rows.Count
Count = 0
For j = 1 To Len(Selection.Cells(i, Column_number))
If Mid(Selection.Cells(i, Column_number), j, Len(Text)) = Text Then
Count = Count + 1
Exit For
End If
Next j
If Count > 0 Then
Selection.Cells(i, Column_number).EntireRow.Delete
i = i - 1
End If
Next i
Exit Sub
Message:
MsgBox "Please Enter a Valid Integer as the Column Number"
End Sub⧪ Note:
This code produces a Macro called Delete_Rows_with_Specific_Text.
- Thereafter, save the Workbook as an Excel Macro-Enabled Workbook. Then come back to your worksheet and select your data set.
- In this case, I’ve selected my data set B4:D13. As I will delete the books with the text “History”, I have marked them in light brown.
- Afterward, press ALT+F8 to open the Macro box. Then, run the Macro Delete_Rows_with_Specific_Text.
- As a result, you will get two Input Boxes. The 1st box will ask you to enter the number of the column where the condition lies.
- In this example, it is 1 (Book Name).
- The 2nd Input box will ask you to enter the specific text. In this example, it is History.
- After providing all two inputs, click OK.
- Consequently, we will get the rows that contain the specific text (History in this example) deleted automatically.
Related Content: How to Delete Hidden Rows in Excel
Thing to Remember
The punchline of all the codes that we used here is:
Selection.Cells(i, Column_number).EntireRow.Delete- Here, Selection.Cells(i, Column_number) denote the cell within the selected range with row number=i and column_number=Column_number.
- Selection.Cells(i, Column_number).EntireRow selects the entire row of the cell.
- And, Selection.Cells(i, Column_number).EntireRow.Delete deletes the whole row.
Download Practice Workbook
Conclusion
Using these methods, you can delete rows within a range with VBA in Excel. Do you have any questions? Feel free to ask us.
Related Articles
- Applying VBA Code to Delete Rows Based on Multiple Cell Value
- Excel VBA to Delete Rows with Specific Data
- How to Delete Selected Rows with Excel VBA
- How to Use VBA to Delete Empty Rows in Excel
- How to Use Macro To Delete Rows Based on Criteria in Excel
- How to Filter and Delete Rows with VBA in Excel
- How to Delete Unfiltered Rows in Excel Using VBA
- How to Delete Hidden Rows in Excel VBA
