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.
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.
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.
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:
You'll see the unused rows disappear in a snap.
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.
If that's your case, you need to format your data as Excel Table first.
You will see the Create Table dialog box that you can use to highlight the necessary range.
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.
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.
Finally click on the Filter icon again to clear it and see that the rows with the values disappeared from your table.
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.
That's it! The rows with identically colored cells are removed in an instant.
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.
Select the found values in the window keeping the Ctrl key pressed. You will get the found values automatically highlighted in your table.
Voila! The unwanted rows are deleted.
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
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!
13 responses to "How to remove rows in Excel based on a cell value"
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.
Hello, Benudhar,
For me to be able to assist you better, please send me a sample table with your data in Excel and the result you want to get. Thank you.
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.
Hi JOHN,
Please look at the following video, it should help:
https://www.youtube.com/watch?v=tmA3oI0LhPg
Thank you. very helpful!
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………………
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.
This was awesome! saved me a lot of time! thanks!
2016 Excel spreadsheet that has random cells with red font. Those cells are highlighted. Need to preserve the rows if they have a single cell highlighted. If the row does not have any highlighted cell, delete that row. Is that possible?
RemoveRowsWithSelectedCells - like this except RemoveRowsWithoutSelectedCell
I have a spreadsheet from an app that transferred all my cell # and added name, phone # address etc.
I would like to:
clean this up big time to make a mailing list.
some contacts have -- home address,
Is there a way to remove the entire row if there is no address in a certain cell?
example
a first name
b second name
cell H is primary street no address no further information
I want to weed all of these people out of this excel
am I crazy for wanting this? #55&annoyed
Hello Gina Ann!
You can apply a filter to your table to display only those rows that contain empty cells in a certain column. Please see here how to create and use Excel Advanced Filter.
Then select all the rows you need to delete at once, right-click and choose the Delete option from the context menu.
I also recommend looking through carefully the "Delete rows that contain certain text in a single column" paragraph above on this page.
Is there a way to use a Macro to delete all rows that have a non-colored cell? I need to leave only the row not changed by the conditional formatting in one particular column. Ay help is appreciated.
I have a spreadsheet having a specific color in column. I want to identify that cell & delete that column using VBA code. How can I do it ?