How to Delete Rows in a Range with VBA in Excel (3 Easy Ways) (2023)

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 theVBA codes that we will use here.

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

Download Practice Workbook

3 Easy Ways to Delete Rows in a Range with VBA in Excel

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 of a bookshop called Martin Bookstore. Today, our objective is to delete rows from this data set with VBA code following different conditions.

1. Run a VBA Code to Delete Every nth Rows in a Range in Excel

You can run a VBA code to remove every nth row from your data set in Excel.

⧪ Steps:

• Firstly, press ALT+F11 on your keyboard.
• As a result, the VBA window will open.

• Secondly, go to the Insert tab in the VBA window.
• From the options available, select Module.

• Consequenlty, 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 MessageDim Count As IntegerCount = 0n = Int(InputBox("Enter the Value of n: "))For i = n To Selection.Rows.Count Step nSelection.Cells(i - Count, 1).EntireRow.DeleteCount = Count + 1Next iExit SubMessage:MsgBox ("Please Enter a Valid Integer.")End Sub

⧪ Note: This code produces a Macro called Delete_Every_nth_Row.

• Thereafter, save the workbook as 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 Every nth Row in Excel (Easiest 6 Ways)

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 MessageColumn_number = Int(InputBox("Enter the Number of Column Where the Condition is Applied: "))Dim Condition As StringCondition = 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 ThenGoTo MessageEnd IfValue = InputBox("Enter the Value: ")If Condition <= 4 ThenValue = Int(Value)ElseIf VarType(Selection.Cells(1, Column_number)) <> 8 ThenValue = Int(Value)End IfEnd IfFor i = 1 To Selection.Rows.CountIf Condition = 1 ThenIf Selection.Cells(i, Column_number) > Value ThenSelection.Cells(i, Column_number).EntireRow.Deletei = i - 1End IfElseIf Condition = 2 ThenIf Selection.Cells(i, Column_number) >= Value ThenSelection.Cells(i, Column_number).EntireRow.Deletei = i - 1End IfElseIf Condition = 3 ThenIf Selection.Cells(i, Column_number) < Value ThenSelection.Cells(i, Column_number).EntireRow.Deletei = i - 1End IfElseIf Condition = 4 ThenIf Selection.Cells(i, Column_number) <= Value ThenSelection.Cells(i, Column_number).EntireRow.Deletei = i - 1End IfElseIf Condition = 5 ThenIf Selection.Cells(i, Column_number) = Value ThenSelection.Cells(i, Column_number).EntireRow.Deletei = i - 1End IfElseIf Condition = 6 ThenIf Selection.Cells(i, Column_number) <> Value and Selection.Cells(i, Column_number)<>"" ThenSelection.Cells(i, Column_number).EntireRow.Deletei = i - 1End IfEnd IfNext iExit SubMessage: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 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 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.

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

Read More: How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)

Similar Readings:

• How to Delete Unused Rows in Excel (8 Easy Ways)
• Delete Alternate Rows in Excel (5 Quick Ways)
• Formula to Remove Blank Rows in Excel (5 Examples)
• Excel Shortcut to Delete Rows (With Bonus Techniques)
• How to Delete Hidden Rows in Excel (3 Methods)

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 MessageColumn_number = Int(InputBox("Enter the Number of the Column Where the Text Lies: "))Text = InputBox("Enter the Specific Text: ")Dim Count As IntegerFor i = 1 To Selection.Rows.CountCount = 0For j = 1 To Len(Selection.Cells(i, Column_number))If Mid(Selection.Cells(i, Column_number), j, Len(Text)) = Text ThenCount = Count + 1Exit ForEnd IfNext jIf Count > 0 ThenSelection.Cells(i, Column_number).EntireRow.Deletei = i - 1End IfNext iExit SubMessage: 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 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 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.

Read More: Excel VBA to Delete Rows with Specific Data (9 Examples)

Thing to Remember

The punchline of all the codes that we used here is:

• Here, Selection.Cells(i, Column_number) denotes 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.

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

• How to Delete All Rows Below a Certain Row in Excel (6 Ways)
• VBA to Delete Entire Row based on Cell Value in Excel (3 Methods)
• VBA Macro to Delete Row if Cell Contains Value in Excel (2 Methods)
• Delete Multiple Rows with VBA in Excel (4 Ways)
• How to Delete a Row If a Cell is Blank in Excel (4 Methods)