Follow along with this ultimate Excel tutorial to learn how to delete multiple rows in Excel.

Often, you need to remove unnecessary, blank, and erroneous rows from your Excel worksheet to make the dataset organized, clean, and suitable for further analytics purposes.

There are many ways to delete such rows in Excel. Find below various techniques for specific scenarios to remove rows using manual, semi-automatic, and fully automatic methods. Let’s dig in!

Using the Manual Method

If your dataset is small, this is the best way to remove multiple redundant rows from an Excel dataset.

Click on the first-row number you need to delete in a series of many rows.

Press the Ctrl key.

Now, select the rest of the row numbers you want to delete.

Right-click on any of these selected row numbers and click on the Delete option in the context menu.

Using the Delete Tool

The Delete tool of the Cells commands block in the Home tab of the Excel desktop app allows you to manually remove rows using the Delete Sheet Rows command.

To use this command, select the multiple range of rows you want to delete from your worksheet.

Click on the Delete drop-down menu in the Cells commands block and choose Delete Sheet Rows.

Excel will get rid of the selected rows in a flash.

Using Keyboard Shortcuts

This is the quickest way to delete multiple rows in Excel for a small dataset.

After highlighting the rows to be deleted, press Ctrl + minus sign (-) to delete all the selected rows.

Excel will remove those rows and automatically highlight the next set of rows.

Using the Find and Replace Tool

Suppose, you want to remove multiple rows based on a specific cell value, text string, etc. In that scenario, you can use the Find and Replace tool to quickly locate and remove the target rows without manually scanning through the entire dataset with the naked eye.

For example, in the above dataset, you need to delete all the rows containing the text string North America.

Bring up the Find and Replace tool by pressing Ctrl + F on the target worksheet.

Click on the Find what box and type the query string, like North America.

Click on the Search drop-down menu and choose By Rows.

Click on the Look in drop-down menu and choose Values.

Hit the Find All button.

You’ll see a list of search results. Select all of the entries in that list.

Excel will highlight the specific cells that contain the search text string.

Right-click on any of the selected cells as shown in the screenshot and click Delete in the context menu.

You’ll now see the Delete dialog box.

Select the Entire row radio button and hit the OK button to delete multiple rows in the dataset.

Using the Sort & Filter Tool

The Sort & Filter tool in Microsoft Excel allows you to filter a select dataset based on the cell value, cell color, or presence of blank cells.

So, if you need to delete multiple rows based on cell value criteria or based on blank cells, this is the best technique to follow for medium datasets.

Navigate to the target worksheet and select the header row of the dataset.

Now, press Ctrl + Shift + L to show Sort & Filter drop-down arrows for all the column headers.

In the practice dataset, as shown above, let’s say you want to delete rows by the Product Category column.

Click on the drop-down arrow of Product Category.

Uncheck the Select All checkbox and checkmark by which product you want to filter the dataset.

Let’s say you want to filter by Books.

As soon as you choose this filter, Excel hides all deselected values from the Product Category column.

Now, follow the steps mentioned in the first method discussed earlier in this article to delete the filtered rows.

Now, press Ctrl + Shift + L to disable filtering and reveal the whole dataset minus the rows that contained Books as the product categories.

Using the Go To Special Command

If you’re wondering how to delete multiple blank rows in Excel without writing a single line of code or constructing complex formulas, you can use the Go To Special tool.

Let’s consider, you want to delete all the blank rows in the above dataset.

Select the entire dataset and press Ctrl + G to bring up the Go To dialog box.

Click on the Special button at the bottom right corner to access the Go To Special menu.

Select the Blanks radio button and click OK.

You should now see that Excel has selected all the blank rows in the selected cell range.

Press the Ctrl + - keys together to delete these blank rows instantly.

On the Delete dialog box, choose Entire row.

The above screenshot shows that you’ve deleted multiple blank rows in Excel using Go To Special.

Using Power Query

Suppose, you’re importing an external database to Excel. However, a single Excel worksheet is unable to accommodate the whole dataset because the rows and columns are more than 1,048,576 and 16,384 respectively.

📒 Read More: How Many Rows Can Excel Handle?

However, you’ve noticed that if you remove blank rows, incomplete rows, and cells with error messages, you can bring down the database size to accommodate it within one worksheet. Here comes Power Query at your rescue.

You can load a gigantic database on Power Query, clean it, and export it to an Excel worksheet for further data analytics purposes.

Here’s how you can use this method to delete multiple rows:

Go to the Data tab and click on the Get Data command.

On the context menu, hover the cursor over an appropriate data import tool, like From File, From Database, From Azure, and more.

On the overflow menu, click on the database source, like From SQL Server Database, From Oracle Database, etc.

Follow onscreen instructions to complete the database import process.

You should see your database in the Power Query Editor tool as shown above.

Go to the Reduce Rows commands block in the Home tab and click on the Remove Rows drop-down.

Here, you’ll find the following options to rationalize the number of rows of the database using the following tools fit for different scenarios:

• Remove Top Rows
• Remove Bottom Rows
• Remove Alternate Rows
• Remove Duplicates
• Remove Blank Rows
• Remove Errors

You can use any of the above options if your scenario matches.

I used the filter tool to remove Electronics and Clothing from the Product Category column in the Power Query Editor.

I clicked on the Product Category drop-down menu and unchecked the checkboxes for Electronics and Clothing.

Then, I applied the filtering rule by clicking on the OK command.

I used the Close & Load To command from the File menu to export the filtered database to a new worksheet.

On the Import Data dialog, I selected the New worksheet option and clicked OK.

Find above a screenshot of the dataset after removing multiple rows on Power Query.

Using Excel VBA

If you find the methods explained so far manual and time-consuming, you can use Excel VBA to automate up to 90% of the task of deleting multiple rows in Excel.

It doesn’t matter whether you know or don’t know how Excel VBA works. I’ll provide the codes you need to create VBA macros. Then, you can run those macros by following simple instructions to complete an hour-long task in Excel in less than a minute.

Firstly, read the following Excel tutorial to learn the quick steps to create a VBA macro from a VBA script.

📒 Read More: How To Use The VBA Code You Find Online

Now try a specific VBA script to automate different ways to delete multiple rows in Excel.

Delete Rows Based on Cell Values

You can use the following VBA script to set up a VBA macro that will delete multiple rows based on custom inputs from you.

Sub DeleteRowsByValue()

  Dim colNumber As Integer
  Dim cellValue As String
  Dim lastRow As Long
  Dim currentRow As Long
  Dim sheetName As String

  ' Get sheet name from user input
  sheetName = InputBox("Enter the name of the sheet to search:")
  If IsEmpty(sheetName) Then Exit Sub ' Exit if user cancels

  ' Get column number from user input
  colNumber = InputBox("Enter the column number to search (e.g., 2 for column B):")
  If IsEmpty(colNumber) Then Exit Sub ' Exit if user cancels

  ' Get cell value to match from user input
  cellValue = InputBox("Enter the value to match in the selected column:")
  If IsEmpty(cellValue) Then Exit Sub ' Exit if user cancels

  ' Get the worksheet object based on user input
  Dim ws As Worksheet
  Set ws = ThisWorkbook.Worksheets(sheetName) ' Set the worksheet based on entered name

  ' Error handling for invalid sheet name
  On Error Resume Next
  If ws Is Nothing Then ' Check if worksheet exists
    MsgBox "Sheet '" & sheetName & "' not found!", vbExclamation
    Exit Sub ' Exit if sheet not found
  End If
  On Error GoTo 0 ' Resume normal error handling

  ' Get the last row of data
  lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Find last row with data in column A

  ' Loop through rows from bottom to top to avoid shifting rows during deletion
  For currentRow = lastRow To 2 Step -1
    If ws.Cells(currentRow, colNumber).Value = cellValue Then ' Use the worksheet object
      ws.Rows(currentRow).Delete Shift:=xlUp ' Delete row and shift remaining rows up
    End If
  Next currentRow

  MsgBox "Matching rows deleted successfully!", vbInformation

End Sub

For example, you’d like to delete all the rows that have the value 2 in column E of the above dataset.

Press Alt + F8 to bring up the Macro dialog box.

Choose the DeleteRowsByValue macro and hit Run.

Once you run the script, Excel asks you for the target worksheet. You enter the worksheet name.

Then, you must enter the numerical value of the target column. For example, if the target column is E, enter the numerical value 5.

Finally, enter the cell value in numerical, like a number 2 in this exercise.

As soon as you execute, Excel deletes all the rows from the example dataset that contains the value 2 in column E.

Delete Multiple Blank Rows

If you create a VBA macro using the following script, upon execution of the macro, you’ll delete all blank rows in the worksheet tables instantly.

Sub DeleteBlankRowsInAllTables()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim tblRow As ListRow
    Dim col As ListColumn
    Dim i As Long
    
    ' Loop through each worksheet in the current workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if the worksheet has any tables (ListObjects)
        If ws.ListObjects.Count > 0 Then
            For Each tbl In ws.ListObjects
                ' Loop through each row in the table
                For i = tbl.ListRows.Count To 1 Step -1
                    Set tblRow = tbl.ListRows(i)
                    ' Check if all cells in the row are blank
                    Dim isBlank As Boolean
                    isBlank = True
                    For Each col In tbl.ListColumns
                        If Not IsEmpty(tblRow.Range(col.Index)) Then
                            isBlank = False
                            Exit For
                        End If
                    Next col
                    
                    ' If all cells in the row are blank, delete the row
                    If isBlank Then
                        tblRow.Delete
                    End If
                Next i
            Next tbl
        End If
    Next ws
End Sub

Before running the script, my dataset looked like the above screenshot.

After running this script, my Excel table looks as shown above.

Delete Rows Based on Date Ranges

Suppose, there’s a column in your Excel worksheet containing many data entries. You’d like to restructure the dataset by deleting some outdated entries by rows.

In this scenario, use the following VBA script to set up a macro:

Sub DeleteRowsByDateRange()
    Dim ws As Worksheet
    Dim colName As String
    Dim dateRange As String
    Dim startDate As Date
    Dim endDate As Date
    Dim cell As Range
    Dim lastRow As Long
    Dim i As Long
    
    ' Prompt for worksheet name
    On Error Resume Next
    Set ws = Worksheets(InputBox("Enter the worksheet name:"))
    On Error GoTo 0
    
    If ws Is Nothing Then
        MsgBox "Worksheet not found. Please enter a valid worksheet name.", vbExclamation
        Exit Sub
    End If
    
    ' Prompt for column name or number containing date entries
    colName = InputBox("Enter the column name or number (e.g., A or 1) with date entries:")
    
    ' Prompt for date range
    dateRange = InputBox("Enter the date range (e.g., 1/1/2024 to 1/2/2024):")
    If InStr(dateRange, " to ") > 0 Then
        startDate = CDate(Split(dateRange, " to ")(0))
        endDate = CDate(Split(dateRange, " to ")(1))
    Else
        MsgBox "Invalid date range format. Please use 'start date to end date'.", vbExclamation
        Exit Sub
    End If
    
    ' Find last row in the specified column
    lastRow = ws.Cells(ws.Rows.Count, colName).End(xlUp).Row
    
    ' Loop through rows and delete if date falls outside the range
    For i = lastRow To 2 Step -1
        Set cell = ws.Cells(i, colName)
        If IsDate(cell.Value) Then
            If cell.Value >= startDate And cell.Value <= endDate Then
                cell.EntireRow.Delete
            End If
        End If
    Next i
    
    MsgBox "Rows deleted outside the specified date range.", vbInformation
End Sub

As soon as you execute this VBA, Excel will guide you through the deletion process of multiple rows by date values with the following visual input boxes:

• Enter the worksheet name for data manipulation on the active workbook.

• Enter column identifier containing date entries. For column B, enter B or 2.

• Enter a date range for the deletion of rows from the entire dataset. Alternatively, you can enter dates separated by commas.

• A confirmation dialog box will pop up if the process is successful.

Delete Rows Based on Text String Inputs

Suppose, there are certain text entries in your Excel worksheet. You’d like to delete those multiple cells along with the rows instantly. Then, use the following VBA script:

Sub DeleteRowsBasedOnText()
    Dim ws As Worksheet
    Dim targetRange As Range
    Dim searchString As String
    Dim cell As Range

    ' Step 1: Select the worksheet
    On Error Resume Next
    Set ws = Worksheets(InputBox("Enter the worksheet name:"))
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "Worksheet not found. Please enter a valid name.", vbExclamation
        Exit Sub
    End If

    ' Step 2: Select the target cell range
    On Error Resume Next
    Set targetRange = Application.InputBox("Select the target cell range:", Type:=8)
    On Error GoTo 0

    If targetRange Is Nothing Then
        MsgBox "No valid range selected. Please try again.", vbExclamation
        Exit Sub
    End If

    ' Step 3: Enter the text string to look for
    searchString = InputBox("Enter the text string to search for:")

    ' Loop through each cell in the target range
    For Each cell In targetRange
        If InStr(1, CStr(cell.value), searchString, vbTextCompare) > 0 Then
            cell.EntireRow.Delete
        End If
    Next cell

    MsgBox "Rows containing '" & searchString & "' have been deleted.", vbInformation
End Sub

When you execute the DeleteRowsBasedOnText macro, Excel will ask you to act on the following prompts:

• Enter the target worksheet name in the prompt.

• Select the target cell range in the worksheet.

• Enter the text string by which you want to delete multiple rows.

When the Excel VBA operation is over, you’ll see a confirmation dialog box as shown above.

⚠️ Warning: Create a copy of your workbook before trying any of the above VBA macros for automation.

Using Office Scripts

The following Office Scripts code will let you delete multiple blank rows in one or many tables in the active worksheet.

To use this script, go to the Automate tab and click on the New Script command.

The Office Scripts Code Editor interface will show up on the right side.

There, delete the existing script if any, and paste the following script:

function main(workbook: ExcelScript.Workbook) {
    let allTables = workbook.getTables();

    allTables.forEach(table => {
        let tableValues = table.getRangeBetweenHeaderAndTotal().getValues();
        let rowsToDelete: number[] = [];
        let rowIndex: number = 0;

        tableValues.forEach(row => {
            if (row.join("") === "") {
                rowsToDelete.push(rowIndex);
            }
            rowIndex++;
        });

        for (let index = rowsToDelete.length - 1; index > -1; index--) {
            table.deleteRowsAt(rowsToDelete[index]);
        }
    });
}

Click on the Save button to save the code for future use.

Now, hit the Run button to execute the script.

The code will delete multiple empty rows in Excel in a flash.

Be mindful that the code will work on all the table objects of the active Excel worksheet.

⚠️ Warning: All the changes made to a worksheet by using an Office Scripts code are irreversible. You can’t use the Excel undo feature to take the worksheet to its previous state. So, create a backup of the workbook before running any scripts from the Automate tab.

Conclusions

Now you know how to delete multiple rows in Excel. Use the Excel user interface-based methods like the Delete command, Find and Replace tool, etc., in small to medium worksheets.

If your dataset is huge, you can try out Go To Special, Excel VBA, and Office Scripts.

When importing gigantic databases to Excel from third-party sources, use Power Query to cleanse and optimize your data.

Comment below to share your experience after trying these tried and methods to delete rows in Excel.