How to Delete Multiple Rows with VBA in Excel (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you how you can delete multiple rows in Excel with VBA. The article will combine deleting multiple rows of a selected range, rows having a specific text, rows maintaining a specific criterion, and rows having blank cells.


How to Delete Multiple Rows with VBA in Excel: 4 Ways

Here we’ve got a data set with the Names of some books and the Prices of a bookshop called Martin Bookstore.

Dataset to Delete Multiple Rows with Excel VBA

Today our objective is to delete multiple rows from this data set with VBA in Excel. Let’s explore 4 different ways to delete multiple rows with VBA in Excel.


1. Delete Multiple Rows of a Selected Range

Step 1:

  • Press ALT+F11 on your keyboard. The VBA window will open.

Step 2:

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

Insert Module

Step 3:

  • A new module window called “Module 1” will open. 
  • Insert the following VBA code in the module.

Code:

Sub Delete_Seletced_Rows()
Dim rng As Range
Set rng = Selection
For i = 1 To rng.Rows.Count
    rng.Cells(i, 1).EntireRow.Delete
Next i
End Sub

This code produces a Macro called Delete_Selected_Rows.

VBA Code to Delete Multiple Rows in excel

Step 4:

  • Save the workbook as an Excel Macro-Enabled Workbook.

Step 5:

  • Return to your worksheet by pressing ALT+Q.
  • Select the rows in your data set that you want to delete.

Excel VBA Delete Multiple Rows

Step 6:

  • Then press ALT+F8 on your keyboard.
  • A dialogue box called Macro will open. Select Count_Rows (The name of the Macro) and click on Run.

Run the Code

Step 7:

Excel VBA Delete Multiple Rows

Read More: How to Delete Rows in a Range with VBA in Excel


2. Remove Multiple Rows with Specific Text Value

You can also delete multiple rows having a specific text from your data set with VBA in Excel.

The steps are also all same as Method 1 (Steps 1-7).

  • Just in Step 3, change the VBA code to this::

Code:

Sub Delete_Rows_with_Specific_Text()
Dim rfText As String
rfText = InputBox("Enter the rfText Value: ")
LrfText = LCase(rfText)
For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Rows.Count
        Words = Split(Selection.Cells(j, 1))
        For Each k In Words
            LWord = LCase(k)
            If LrfText = LWord Then
                Selection.Cells(j, 1).EntireRow.Delete
            End If
        Next k
    Next j
Next i
End Sub

This code creates a module called Delete_Rows_with_Specific_Text.

VBA Code

  • In Step 5, before running the code, select the column with the specific text value. Here I have selected range B4:B13 (Name of the Books).

  • Then press ALT+F8, select Delete_Rows_with_Specific_Text, and click on Run.

  • An Input Box will appear that will ask you to enter the text value that you want to match.

Input Box

  • For this example, I have entered it as “history”.

Excel VBA Delete Multiple Rows

And you will find all the rows having the specific text (“History” in this case) deleted from your data set.

Read More: Excel VBA to Delete Row Based on Cell Value


3. Delete Multiple Rows with Criteria

You can also use a VBA code to delete multiple rows having a specific criterion from your data set.

Let us derive a Macro that will delete the rows with book prices less than or equal to $15.

The steps are also all same as Method 1 (Steps 1-7).

  • Just in Step 3, change the VBA code to this::

Code:

Sub Deleting_Rows_with_Criteria()
For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Rows.Count
        If Selection.Cells(j, 1) <= 15 Then
            Selection.Cells(j, 1).EntireRow.Delete
        End If
    Next j
Next i
End Sub

Note:

  • This code creates a module called Delete_Rows_with_Criteria.
  • In line 5, we used “<=15” because this is the criterion we are using. You change it according to your need.

  • And in Step 5, before running the code, select the range of cells with the criteria. Here I have selected only column C (C4:C13, Price) because the criterion lies there.

Select the values to Delete Multiple Rows

  • Then press ALT+F8, select Delete_Rows_with_Criteria, and click on Run.

Excel VBA Delete Multiple Rows

  • You will find all the rows with book prices less than or equal to $15 deleted automatically.

Excel VBA Delete Multiple Rows

Read More: How to Delete Row If Cell Contains Value Using Macro in Excel


4. Delete Multiple Rows with Blank Cells

Finally, you can also use a VBA code to delete multiple rows having blank cells.

Let us consider for a moment that we don’t know the prices of some books, and they are left empty in the data set.

Excel VBA Delete Multiple Rows

We want to delete the rows with the empty cells.

The steps are all the same as Method 1 (Steps 1-7).

  • Just in Step 3, enter this VBA code in place of the earlier one:

Code:

Sub Deleting_Rows_with_Blank_Cells()
For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Rows.Count
        If Selection.Cells(j, 1) = "" Then
            Selection.Cells(j, 1).EntireRow.Delete
        End If
    Next j
Next i
End Sub

Note:

  • This code creates a module called Deleting_Rows_with_Blank_Cells.

VBA Code

  • In Step 5, before running the code, select the column with the blank cells. Here I have selected range C4:C13 (Price).

Run the Code

  • Then press ALT+F8, select Delete_Rows_with_Blank_Cells, and click on Run.

Select Data to Delete Multiple Rows with VBA in Excel

  • All the rows with the blank cells in your data set will be deleted automatically.

Excel VBA Delete Multiple Rows


Download Practice Workbook


Conclusion

Using these methods, you can delete multiple rows from your data set with VBA in Excel. Do you have any questions? Feel free to ask us.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo