How to remove rows in Excel based on a cell value

This article lists several ways to delete rows in Excel 2010-2013 based on a cell value. In this post you'll find hotkeys as well as Excel VBA. Delete rows automatically or use the standard Find option in combination with helpful shortcuts.

Excel is a perfect tool to store data that change every now and then. However, updating your table after some changes may need really much time. The task can be as simple as removing all blank rows in Excel. Or you may need to find and delete the duplicated data. One thing we know for sure is that whenever details come or go, you search for the best solution to help you save time on the current work.

For example, you have a marketplace where different vendors sell their products. For some reason one of the vendors closed their business and now you need to delete all rows that contain the vendor's name, even if they are in different columns.

In this post you'll find Excel VBA and shortcuts to delete rows based on certain text or value. You'll see how to easily find and select the necessary information before removing. If your task is not about deleting but adding rows, you can find how to do it in Fastest ways to insert multiple rows in Excel 2010-2013.

The fastest Excel shortcut to delete rows in your table

If you want to use the fastest method of deleting multiple rows according to the cell value they contain, you need to correctly select these rows first.

To select the rows, you can either highlight the adjacent cells with the needed values and click Shift + Space or pick the needed non-adjacent cells keeping the Ctrl key pressed.

You can also select entire lines using the row number buttons. You'll see the number of the highlighted rows next to the last button.
Select entire lines using the row number buttons

After you select the necessary rows, you can quickly remove them using an Excel "delete row" shortcut. Below you'll find how to get rid of the selected lines whether you have a standard data table, or a table that has data to the right.

Remove rows from the entire table

If you have a simple Excel list that has no additional information to the right, you can use the delete row shortcut to remove rows in 2 easy steps:

  1. Select the rows you want to delete.
  2. Press the Ctrl + - (minus on the main keyboard) hotkey.

You'll see the unused rows disappear in a snap.

Tip. You can highlight only the range that contains the values you want to remove. Then use the shortcut Ctrl + - (minus on the main keyboard) to get the standard Excel Delete dialog box allowing you to select the Entire row radio button, or any other deleting option you may need.
Excel Delete dialog box

Delete rows if there is data to the right of your table

Ctrl + - (minus on the main keyboard) Excel shortcut is the fastest means to delete rows. However, if there is any data to the right of your main table like on the screenshot below, it may remove rows along with the details you need to keep.
You may have data to the right of your main table

If that's your case, you need to format your data as Excel Table first.

  1. Press Ctrl + T, or go to the Home tab -> Format as Table and pick the style that suites you best.
    Go to the Home tab -> Format as Table button

    You will see the Create Table dialog box that you can use to highlight the necessary range.
    Use the Create Table dialog box to select the necessary range

  2. Now that your list is formatted, select the range with the values or rows you want to delete within your table.
    Select a range with the values to delete

    Note. Please make sure you don't use the row buttons to select the entire rows.
  3. Press Ctrl + - (minus on main keyboard) to see the unwanted data removed only from your table. The additional information to the right will be left intact.

Hope you've found this "remove row" shortcut helpful. Continue reading to find Excel VBA for deleting rows and learn how to eliminate data based on certain cell text.

Delete rows that contain certain text in a single column

If the items in the rows you want to remove appear only in one column, the following steps will guide you through the process of deleting the rows with such values.

  1. First you need to apply Filter to your table. To do this, navigate to the Data tab in Excel and click on the Filter icon.
    Click on the Filter icon in Excel
  2. Filter the column that contains the values for deleting by the needed text. Click on the arrow icon next to the column that contains the needed items. Then uncheck the Select All option and tick the checkboxes next to the correct values. If the list is long, just enter the necessary text in the Search field. Then click OK to confirm.
    Filter the column by the needed text
  3. Select the filtered cells in the rows you want to delete. It's not necessary to select entire rows.
  4. Right-click on the highlighted range and and pick the Delete row option from the menu list.
    Pick the Delete Row option from the menu list

Finally click on the Filter icon again to clear it and see that the rows with the values disappeared from your table.

How to remove rows in Excel by cell color

The filter option allows sorting your data based on the color of cells. You can use it to delete all rows that contain certain background color.

  1. Apply Filter to your table. Go to the Data tab in Excel and click on the Filter icon.
    Click on the Filter icon in Excel
  2. Click on the small arrow next to the needed column name, go to Filter by Color and pick the correct cell color. Click OK and see all highlighted cells on top.
    Click on the Filter arrow, go to Filter by Color and pick the needed cell color
  3. Select the filtered colored cells, right-click on them and pick the Delete Row option from the menu.
    Select the Delete Row option from the list

That's it! The rows with identically colored cells are removed in an instant.

Delete rows that contain certain text in different columns

If the values you want to remove are scattered around different columns, sorting may complicate the task. Below you'll find a helpful tip to remove rows based on the cells that contain certain values or text. From my table below, I want to remove all rows that contain January which appears in 2 columns.

  1. Start by searching and selecting the cells with the needed value using the Find and Replace dialog. Click Ctrl + F to run it.
    Tip. You can find the same dialog box if you go to the Home tab -> Find & Select and pick the Find option from the drop-down list.

    Go to the Home tab -> Find & Select and pick the Find option

  2. Enter the needed value in the Find what field and select any additional options if necessary. Then press Find All to see the result.
    Enter the needed value in the Find what field
  3. The results will appear in the Find and Replace window.
    See the results in the Find and Replace window

    Select the found values in the window keeping the Ctrl key pressed. You will get the found values automatically highlighted in your table.
    See the found values automatically highlighted in your table

  4. Now navigate to the Home tab -> Delete -> Delete Sheet Rows.
    Navigate to the Home tab -> Delete -> Delete Sheet Rows

    Tip. You can delete the rows with the selected values if you press Ctrl + - (minus on the main board) and select the radio button Entire rows.

Voila! The unwanted rows are deleted.

Excel VBA macro to delete rows or remove every other row

If you always search for a solution to automate this or that Excel routine, grab the macros below to streamline your delete-rows task. In this part you'll find 2 VBA macros that will help you remove rows with the selected cells or delete every other row in Excel.

The macro RemoveRowsWithSelectedCells will eliminate all lines that contain at least one highlighted cell.

The macro RemoveEveryOtherRow as its name suggests, will help you get rid of every second/third, etc., row according to your settings. It will remove rows beginning with the current mouse cursor location and till the end of your table.

If you don't know how to insert macros, feel free to look at How to insert and run VBA code in Excel 2010, 2013 - tutorial for beginners.

Sub RemoveRowsWithSelectedCells()
    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


Sub RemoveEveryOtherRow()
    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

Tip. If your task is to color every second/third, etc., row with a different color, you will find the steps in Alternating row color and column shading in Excel (banded rows and columns).

In this article I described how to delete rows in Excel. Now you have several useful VBA macros to delete the selected rows, you know how to remove every other row and how to use Find & Replace to help you search and select all the lines with the same values before eliminating them. Hope the tips above will simplify your work in Excel and let you get more free time for enjoying these last summer days. Be happy and excel in Excel!

You may also be interested in:

7 Responses to "How to remove rows in Excel based on a cell value"

  1. Benudhar Choudhury says:

    I need to fetch data of a single coloum from a web page,but I am getting the complete page.Please tell me how do I fix it so that i will get only a single data which will update while refreshing.

  2. JOHN DE BRAGANZA says:

    Hello,
    I have a master file with data of various stores which needs to be split into various sheets store wise. I have managed to split it, but I don't have a code to delete the blank rows in between the data.

  3. rechel says:

    Thank you. very helpful!

  4. revanthraj says:

    i have 300 column in sheet1,but i need only 3 column from that to sheet2.out of that 3 column i need to filter for some values and the filtered values only need to display other values are not need …that are must hide or delete.. anybody help me………………

  5. Pitamber says:

    Hello Maria,

    I have a excel sheet, I have been able to filter the data and get some cells into red (to be deleted) and green (to be used) using macro. I am looking for a macro by which I can delete the complete row if a cell is red.

    The red and green colors are same which we get using conditional formatting in excel 2013.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard