How To Delete Single and Multiple Rows in Excel

As an Excel user, you may need to delete multiple rows when cleaning up, restructuring, or streamlining information.

Excel provides various methods to perform this task, whether you're working with adjacent rows, non-adjacent rows, or rows that meet specific criteria. In this guide, we’ll cover all the best ways to delete multiple rows in Excel.

How To Delete a Single Row in Excel

Let's start by showing you how to remove a single row in Excel. Follow the steps below:

1. Select the row: Click the row number on the left-hand side of the sheet to highlight the entire row you want to delete.

   

2. Delete the row:

   • Using the Right-Click Menu: Right-click the selected row and choose "Delete" from the context menu.

     

   • Using the ribbon: Go to the "Home" tab, find the "Cells" group, and click the "Delete" dropdown. Then select "Delete Sheet Rows".

     

   • Using keyboard shortcuts: Press Ctrl + - to delete all selected rows.

3. Confirm the change: The row will be removed, and the rows below will shift up to fill the gap.

   

How To Delete Multiple Rows in Excel (Adjacent Rows)

Excel allows users to delete multiple rows without fuss. Here's how to delete contiguous rows in Excel:

1. Select the rows: Click and drag on the row numbers on the left-hand side of the sheet to highlight all the adjacent rows you want to delete.

   

2. Delete the rows:

   • Using the right-click menu: Right-click on the selected rows and click "Delete."

     

   • Using the ribbon: In the Cells group, click Delete > Delete Sheet Rows.

     
   • Using keyboard shortcuts: Press Ctrl + - (minus) on your keyboard.

3. Confirm the change: Excel will delete the highlighted. Check to see if you made any error.

   

How To Delete Rows Based on Specific Criteria

You can delete rows based on specific criteria in Excel by using filters. Here’s how:

1. Apply a filter: Select your dataset. Go to the Data tab and click Filter.

   

2. Filter by criteria: Click the dropdown arrow in the column header containing your criteria. Then, choose the condition (e.g., filter out rows with "IT" in the Department column).

   

3. Select and delete rows: Right-click and choose "Delete Row", or go to the Home tab, select "Delete", and click "Delete Sheet Rows".

   

4. Clear the filter: Return to the Data tab and click Clear to remove the filter. Only the rows without "IT" in the Department column will remain.

   

How To Delete Multiple Blank Rows in Excel

The Go To Special feature quickly locates and selects specific types of cells in your Excel worksheet, such as blank cells, cells with formulas, or cells with conditional formatting. Here's how to use it to delete empty rows:

1. Press Ctrl + G, then click Special.

   

2. Select Blanks and click OK.

   

3. Delete the highlighted rows using Ctrl + -.

   

How To Delete Multiple Rows in an Excel Table

Excel tables have structured formatting that affects row deletion. So, you have to be careful to maintain your data structure.

1. Select the rows you want to delete.

   

2. Right-click and choose Delete Table Rows to ensure table formatting remains intact.

   

Automating Row Deletion With Excel VBA Macro (AI Method)

When handling large datasets, you can use VBA (Visual Basic for Applications) to automate row deletion. If you don't know how to write VBA code, don't worry. You can ask an AI tool like Claude or ChatGPT to write one.

For instance, you can ask ChatGPT to "write a VBA code to delete alternate rows in Excel."

1. Press Alt + F11 to open the VBA editor.

2. Go to Insert > Module and paste the ChatGPT's code:

   Sub DeleteAlternateRows()

   Dim ws As Worksheet

   Dim lastRow As Long

   Dim i As Long

   ' Set the worksheet you want to work on

   Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

   ' Find the last row with data in the worksheet

   lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

   ' Loop through the rows, starting from the second row (or any other row as needed)

   For i = lastRow To 2 Step -2 ' Step -2 to delete alternate rows

   ws.Rows(i).Delete

   Next i

   MsgBox "Alternate rows deleted successfully!", vbInformation

   End Sub

   Close the VBA editor and return to Excel.

3. Press Alt + F8, select the DeleteAlternateRows macro, and click Run.

Final Thoughts 

Whether you’re cleaning up a small spreadsheet or managing extensive datasets, Excel offers versatile tools to delete multiple rows efficiently. There's a method for every situation, from simple shortcuts to advanced VBA scripts. Experiment with these techniques to find what works best for you!

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

FAQ

1. Can I undo a row deletion in Excel?
Yes, you can undo a row deletion by pressing Ctrl + Z immediately after deleting the rows. This restores the rows along with their data.

2. Why can’t I delete rows in an Excel table?
The "Delete" option may be greyed out if the worksheet is protected or if the table is linked to external data. Check for these restrictions and resolve them before attempting to delete rows.

3. Is it possible to delete rows without affecting the rest of the table's structure?
Yes, when working in an Excel table, use the "Delete Table Rows" option to maintain the table’s structure. This ensures the table formatting and formulas remain intact.

Related Articles

How to Add and Remove a Watermark in Excel

How to unhide an Excel Workbook

How to Check if a Cell Is Not Blank in Excel