If you’re looking to learn how to delete a row in Excel in different ways, this is the best tutorial you can get online!

Microsoft Excel isn’t just a superb software for data analysis and visualization. It’s also an excellent and trusted app to store data for long-term preservation. In a few months, your workbook can become truly extensive with many worksheets and within these worksheets rows and columns.

As you keep adding data to your Excel workbook, you also delete unnecessary data to keep the file objective and clutter-free. Here comes the need to delete a row, remove multiple rows, or only remove blank rows in Excel.

Read this guide until the end to find out ways to delete a row in Excel that suits different personal and business needs. Let’s dive in!

Reasons to Delete a Row in Excel

Find below the most common situations when you’ll truly appreciate this skill you’re learning today:

• Suppose, you’re maintaining a database of third-party vendors you often use to procure materials for your business. A few of the vendors have closed their businesses. So, you no longer need to keep those vendors in your workbook. Here, deleting rows can help you update the Excel file.
• Deleting rows with sensitive or confidential information ensures that unauthorized users do not have access to that data.
• Deleting irrelevant or redundant rows improves the readability of your spreadsheet, making it easier for others to understand.
• If there are errors or inaccuracies in your data, deleting the corresponding rows can be a corrective action.
• Delete rows with incorrect or incomplete information to ensure the accuracy of your dataset.
• You can also remove unnecessary rows to simplify calculations or create more concise visualizations.
• When you delete unused rows, you reduce the file size and increase the speed of operations, especially with large datasets.
• Last but not least, you might want to eliminate rows that you used for testing purposes or those rows no longer serve a purpose in the final dataset.

Now discover below all the popular and smart methods to remove a row in Excel:

Delete a Row Using the Delete Tool

The Delete command in the Cells block of the Home tab allows you to remove cells, rows, columns, and sheets. Although, deleting a cell is the same as deleting a row. Find below how to use this method:

1. Go to your Excel worksheet and highlight a cell you want to remove.
2. Remember that the whole row will be deleted even if you select one cell or the whole row.
3. Go to Home > Cells > Delete.
4. Click on the Delete drop-down list and choose Delete Sheet Rows.
5. Excel will remove the selected data as well as its row.

In the above example, if you choose Delete Cells, then Excel also deletes the entire row since this is an Excel Table object.

However, if it’s a simple Excel dataset without table formatting, you’ll see a dialog box. Where you need to choose between Shift cells left or Shift cells up should you want to delete the highlighted cell.

Delete a Row Using the Right-Click Context Menu

Another quick way to remove a few rows from the worksheet is by using the right-click menu on the highlighted row.

Firstly, if you’ve already identified a row you want to delete, for example, row 6, right-click on the row number. Then, click Delete on the context menu.

Secondly, if you don’t know which row it is but you’ve identified the cell, again right-click. Then choose Delete from the context menu.

The Delete dialog will pop up. Here, choose the Entire row option to delete the row of the selected cell.

Deleting Non-Contiguous Rows in Excel

The right-click method is also useful when you need to delete non-adjacent rows in the worksheet.

Highlight the rows you want to delete by clicking on the row numbers when pressing the Ctrl key on the keyboard. Now, right-click and choose Delete from the context menu.

Remove a Row Using a Keyboard Shortcut

If you love to navigate on Excel using the keyboard for better productivity you’ll like this method. Here, you can use a combination of Shift, Spacebar, Ctrl, and minus (-) to highlight and delete rows effortlessly.

Firstly, go to a cell of the row you want to delete and hit Shift + Spacebar. This action highlights the whole row.

Now, simply press the Ctrl + minus (-) keys to delete the row.

Delete a Row Based on Cell Color

Suppose, someone before you identified the rows to be deleted in an Excel workbook using Fill Color. Instead of manually deleting those using the right-click method, you can make use of the Sort & Filter tool.

1. Go to the Excel workbook and highlight the column headers of the dataset.
2. Then, go to the Editing commands block on the Home tab.
3. From Editing, go to Sort & Filter and choose Filter from the drop-down list that shows up.

4. You should now see drop-down list arrows on all the column headers you highlighted earlier.
5. If the entire row was highlighted with a specific color previously, you can click the Filter drop-down on any of the column headers.
6. However, if only the cell was highlighted, you need to click the Filter arrow in the column where the cell exists.
7. On the Filter context menu, hover the cursor over the Filter by Color menu.
8. On the overflow context menu, click on the color by which you want to delete a cell.

9. Excel will filter out the cells that don’t have any background color.
10. By now, you’ve found all the rows you want to delete.
11. Highlight all those rows by using a left-click and dragging along the row numbers until all of them are selected.

12. Right-click and choose Delete Row.

13. Now, release the filter of the dataset from Editing > Sort & Filter and click the Filter button on the context menu.

Great one! You’ve successfully deleted a few rows based on their Fill Color attributes.

Also read: 5 Ways to Sort by Color in Microsoft Excel

Delete a Row Using Excel VBA

So far, you’ve learned various manual methods to delete rows in Excel. In this section, I’m going to explain how you can use Excel VBA to automate the process.

Remove Every Other Row

Suppose you want to delete all alternate rows in your Excel worksheet. You can use the following Excel VBA script to achieve this in a few clicks.

The macro will work seamlessly on the active worksheet and selected rows. Here’s how you can create the macro:

1. Press the Alt + F11 keys to launch the Excel VBA Editor.
2. Click Insert and choose Module.
3. On the blank module, copy and paste the following script:

Sub Deletealternaterows()
  Dim rowNo, rowStart, rowFinish, rowStep As Long
  Dim rng2Delete As Range

  rowStep = 2
  rowStart = Application.Selection.Cells(1, 1).Row
  rowFinish = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual

  For rowNo = rowStart To rowFinish Step rowStep
      If Not rng2Delete Is Nothing Then
          Set rng2Delete = Application.Union(rng2Delete, _
           ActiveSheet.Cells(rowNo, 1))
      Else
          Set rng2Delete = ActiveSheet.Cells(rowNo, 1)
      End If
  Next
  If Not rng2Delete Is Nothing Then
      rng2Delete.EntireRow.Delete
      ' Hide every other row
      'rng2Delete.EntireRow.Hidden = True
  End If

  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub

4. Click the Save button.

5. Press the Go back button on the Microsoft Excel dialog box.

6. You should see the Save As dialog.
7. Click the Save as type drop-down list and choose the Excel Macro-Enabled Workbook option.
8. Hit the Save button.
9. Close the Excel VBA Editor.

Now, press the Alt + F8 keys on the keyboard to launch the Macro dialog box. There, select the Deletealternaterows macro and hit the Run button to execute.

Remove Rows Containing Highlighted Cells

For instance, you want to manually highlight a few cells on the worksheet and Excel will remove the corresponding rows. You can do so by creating a VBA macro using this script:

Sub DeleteRowsBasedOnSelectedCells()
    Dim selectedRange As Range
    Dim selectedCell As Range
    Dim rowToDelete As Long
    
    ' Check if any cells are selected
    If Selection.Cells.Count > 0 Then
        ' Loop through each selected cell
        For Each selectedCell In Selection
            ' Get the row number of the selected cell
            rowToDelete = selectedCell.Row
            
            ' Delete the entire row
            Rows(rowToDelete).Delete
        Next selectedCell
    Else
        MsgBox "No cells selected. Please select cells before running this macro.", vbExclamation
    End If
End Sub

Remove Rows Based on Inputs

Suppose, you want Excel to prompt you for rows to be deleted. You can easily do so by using this script:

Sub DeleteRowsBasedOnUserInput()
    Dim rowToDelete As Long
    Dim response As Variant
    
    ' Loop until the user clicks "Cancel"
    Do
        ' Prompt the user for the row number
        response = InputBox("Enter the row number to delete (or click Cancel to exit):", "Delete Row")
        
        ' Check if the user clicked "Cancel" or entered a valid row number
        If response = "" Then
            Exit Sub ' User clicked "Cancel"
        ElseIf IsNumeric(response) Then
            rowToDelete = CLng(response)
            
            ' Check if the row number is within a valid range
            If rowToDelete >= 1 And rowToDelete <= Rows.Count Then
                ' Delete the specified row
                Rows(rowToDelete).Delete
            Else
                MsgBox "Invalid row number. Please enter a valid row number.", vbExclamation
            End If
        Else
            MsgBox "Invalid input. Please enter a numeric value for the row number.", vbExclamation
        End If
    Loop
End Sub

The script will work on the active worksheet. So, if you want to delete a few designated rows on another worksheet of the Excel workbook, navigate to that sheet and then run the macro.

You can create Form Control buttons using the above VBA scripts for increased convenience for you and other users who might access the workbook. Read this excellent article to learn about VBA Form Controls in Excel.

How To Add A Form Control Button To Run Your VBA Code

Conclusions

In conclusion, mastering the art of deleting rows in Excel is a fundamental skill for you. The methods explored in this article provide a diverse toolkit catering to various user preferences and scenarios.

For the swift and straightforward deletion of a row, the Delete tool stands out as a user-friendly option, perfect for you as an Excel learner.

Meanwhile, the right-click context menu offers a quick alternative for you if prefer a mouse-driven approach, simplifying the process with just a few clicks.

If you’re an advanced user and keyboard enthusiast, you’ll appreciate the efficiency of the keyboard shortcut method, allowing for row removal with minimal effort.

For more specialized tasks, such as deleting rows based on cell color, the article introduces a method that adds a layer of customization to data management.

Lastly, if you’re seeking automation and versatility, Excel VBA is the best option. It enables the creation of personalized scripts for row deletion.

Give the above methods a try and comment below which one you liked the most. Also, if I’ve missed a better method to delete a row in Excel, mention that in your comment.