How to Delete Selected Rows in Excel(8 Approaches)

Sometimes we need to remove some unwanted rows from the sheet. We can do it using the delete option. In this article, I’m going to explain how to delete selected rows in Excel.

To make it clearer, I’m going to use a datasheet of sales information of some sales representatives which has 5 columns.

This column represents sales information for different locations. The columns are Sales Rep, Location, Product, Sales, and Order Date.
Sample Datasheet

Workbook to Practice

8 Approaches to Delete Selected Rows in Excel

1. Delete Selected Row Using Ribbon

To delete a selected row using ribbon first select the rows from the sheet you want to delete

Then, open the Home tab >> then go to Delete >> select Delete Sheet Rows.

I selected row number 8.

Using Ribbon to delete selected rows

Now the selected row will be deleted from the sheet.

Selected rows deleted

Read more: How to Delete Rows in Excel

2. Delete Selected Row Using Right Click

To delete selected rows using right-click first, select the rows you want to delete then right-click on the mouse then select the Delete option.

I selected row numbers 7 & 8.

Using right click to delete selected rows

Now, a dialog box will pop up which shows some options for deleting selected rows.

I selected the option Entire row. Finally, click OK.

Selecting delete options

Now, the selected rows will be deleted.

Selected rows deleted

Read more: How to Delete Specific Rows in Excel

3. Delete Selected Rows Using Keyboard Shortcut

First, select the rows you want to delete then press CTRL+ – (minus). Then a dialog box will pop up from where you can select deleting options.

I selected row numbers 10 & 11 and selected the option Entire Row.

Selecting delete option

Now, the selected rows will be deleted.

Selected rows deleted

4. Using Filter to Delete Selected Rows

I. Delete Rows with Particular Text

First, select the cell range where you want to apply filter then open Data tab >> then select Filter.

Using Filter option

Now the Filter will be applied to the selected range.

Filtered Datasheet

Then select a column that contains text values then right-click on the header of the column. Now it will pop up some options to sort values.

I selected the Location column and selected the USA from the sort option.

Sorting Options

Only the rows where the Location is in the USA will be shown.

Sorted Location

Now select any rows and right-click on the mouse then select Delete Rows.

Using Filter to delete selected rows

It will pop up a warning message. Now click OK.

Warning message

Now the selected row will be deleted.

Selected rows deleted

II.Delete Rows Using Number Filters

First, select the cell range where you want to apply filter then open Data tab >> then select Filter.

Using Filter option

Now the Filter will be applied to the selected range.

Filtered Datasheet

Then select a column that contains numeric values then right-click on the mouse on the header of the column. Now it will pop up some options to sort values.

I selected the Sales column.

Using filter to delete selected rows

From Sort options, I selected Number Filters >> then selected Greater Than

Using Filter to delete selected rows depending on numeric value

Now, it will pop up a dialog box. I Inserted the value 9040 to sort rows.

Using Filter to delete selected rows

Then, it will sort all the values greater than 9040.

Sorted Values using greater than

Now select any rows and right-click on the mouse then select Delete >> then select Entire Sheet Row.

Using Filter to delete selected rows

Now the selected rows will be deleted.

Selected rows deleted

Read more: How to Delete Multiple Rows in Excel

5. Using Sort to Delete the Rows

First, select the cell ranges where you want to apply sort then open Data tab >> then select Sort

Using sort option to delete selected rows

Now, it will pop up a dialog box. In Sort by you can choose any column depending on which all values will sort also from Order you can select any order.

I selected the Sales column from Sort by and Smallest to largest from Order.

sorting values depending sales value

Now all the rows are sorted from smallest to largest Sales value.

Values are sorted from smallest to greatest

Now you can select any rows to delete then right-click on the mouse and select the delete option and finally, click OK.

I selected the option Entire row. 

Using delete options to delete selected rows

All selected rows will be deleted.

Selected rows deleted

6. Using Find to Delete the Selected Rows

First, select the ranges from where you want to find a value.

Then open Home tab >> From Find & Select >> then select Find.

Using Find & Select to delete selected rows

Then, a dialog box will pop up. In Find what you can insert the value you want to find finally, click on Find All.

I inserted the USA. All the rows having the value USA is showing there.

Using Find & Select to delete selected rows

Select all the found values in the dialog box. You can use CTRL+A to select all.

Using Find & Select to delete selected rows

Now right-click on any of the selected values in the sheet then select Delete.

Using Find & Select to delete selected rows

Now, It will pop up a dialog box to select deleting options. I selected the option Entire row. Finally, click OK.

Selecting delete option

All the rows having the value USA will be deleted.

Selected rows deleted

Read more: How to Delete Multiple Rows in Excel with Condition

7. Using VBA to Delete Selected Rows

First, open the Developer tab >> then select Visual Basic

Using VBA to delete selected rows

Now, a new window of Microsoft Visual Basic for Applications will appear. Then, open Insert now select the Module.

Microsoft Visual Basic for Application

Now the Module is open.

Module to write VBA code

Now, write the code to delete selected rows in the module.

Sub DeleteSelectedRows()

Dim rngCurCell, rng2Delete As Range




Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual




For Each rngCurCell In Selection

If Not rng2Delete Is Nothing Then

Set rng2Delete = Application.Union(rng2Delete, _

ActiveSheet.Cells(rngCurCell.Row, 1))

Else

Set rng2Delete = rngCurCell

End If

Next rngCurCell




If Not rng2Delete Is Nothing Then

rng2Delete.EntireRow.Delete

End If




Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

End Sub

 

VBA code

After Saving the code go back to the worksheet and select a range to apply the code.

First, open the View tab >> then go to Macros >> select View Macros.

Using Macros to run VBA

Now, a dialog box will pop up. It shows the saved Macro name. Now select the DeleteSelectedRows and the worksheet where I want to apply Macros. Finally, click Run.

Using Macros to delete selected rows

Macros will delete the selected rows.

Selected rows deleted

Read more: Delete Multiple Rows in Excel with VBA

8. Delete Selected Rows With a Blank Cell

In a large datasheet, there is a possibility of having blank rows. I intentionally made two rows blank in this datasheet to show you this approach.

Blank rows

To delete blank rows first, open Home tab >> then from Find & Select >> select Go to Special.

Using Go To Special to delete selected rows

It will pop up a dialog box. From there select Blanks and click OK.

Using Go To Special to delete selected rows

Blank rows are selected.

Using Go To Special to delete selected rows

Now click on the right side of the mouse and select Delete.

Using Go To Special to delete selected rows

Now, a dialog box will pop up and I selected the option Entire row. Then finally, click OK.

Selecting delete options

Now the selected blank rows will be deleted.

Selected rows deleted

Read more: How to Delete a Row If a Cell is Blank in Excel

Conclusion

In this article, I explained 8 approaches to delete selected rows in Excel. I hope these different approaches will help you to delete selected rows. For any kind of suggestions, ideas, the feedback you are most welcome. Feel free to comment down below.


Further Readings:

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo