When you are working with data tables containing information that you received from another person, you may want to prune the amount of data in the table by deleting rows if a particular condition is met. There are several ways you can approach such a task.
The first method is to use Excel's AutoFilter feature. This works particularly well if you have a rather simple criteria by which to delete rows. When you turn on the AutoFilter, Excel places pull-down buttons at the right side of each cell in the data table's header row. Using these pull-down buttons you can specify the records you want displayed. You should select a filter value that will result in displaying only those rows you want to delete. With those rows displayed, you can select them and use the menus to get rid of the rows. When you turn AutoFilter off, then you are left with only the rows you wanted.
Another method involves the use of macros to do the deleting for you. This approach works well if you have to perform the deletions on lots of data, or if you do it quite often. The following macro can delete rows based on a key value:
To use the macro, select the range the key range that covers the rows you want checked. For instance, if the key to be checked is in column G, and you want to check rows 5 through 73, then you would select the range G5:G73. When you run the macro, it asks you what value it should check for. If any cells in the range G5:G73 contain the value you specify, the corresponding row for that cell will be deleted.
There are obviously other ways to delete rows based on a value. For a good selection of different methods, take a look at this page by Dave Hawley at Ozgrid:
Leave your own comment:
Comments for this tip:
Eric 19 Sep 2016, 13:24
hi I have a list of emails where some are not emails. file is huge. could you advise on how to remove cells that is not email?
Ronan 21 Jul 2016, 10:19
can the above code be amended to delete rows greater than a particular date?
Lary 10 Nov 2015, 13:36
I have a document with the following columns "Date", "File Number", Description", and "Group". Due to varying lenghts of the description, there are times when the data gets split across 2 or more rows when it is imported. I would like to be able to run a macro that would recombine the data into the first cell and then delted the superfluous rows, without corrupting the rest of the file. Any Ideas?
ASLAM 12 Oct 2015, 01:24
i need to remove in excel vb with text a1:a9 remove...and a19,a20,a21 also remove with text..and a35,a36,a37 also remove with text up to af remove
Matt 20 Oct 2014, 22:40
I have List1-Parts.
I have a List2-Filter
I want to compare List1-Parts to List2-Filter. then delete not matching rows
In List1-Parts, if Col-A does not begin with any of the 10 values in List1-Filter, I want to delete. I can not seem to get multiple begin with filters to work, Array only seems to be returning the last evaluated value.
Franklin 02 Jun 2014, 12:00
I am trying to delete rows of data from a very large file as well, by using values within a column to identify rows for deletion. To accomplish this I need to read a column containing over a thousand rows of data from another sheet within the same workbook for deletion. Can you help me with code for reading all of the values in from the secondary worksheet at once and perform the row deletion; instead of keying in the value for deletion one at a time.
brett 02 Apr 2013, 20:17
I am trying to delete all rows in an excel spreadsheet containing certain cell values in Column I. The Range of cells containing the values that determine the values that need to be deleted can be found on Sheet 2 (A1:A1000). Please help, if possible. Please let me know if this does not make since and I will attempt to clarify. Thank you in advance.
Barry Fitzpatrick 20 Feb 2013, 07:23
Do you have any error codes when the file crashes? and at which line of code the crash occurs? OR does it just hang (which is my suspicion)? If the macro enters an endless loop it appears to the User that it has "hung", rather than crashed (crashes usually give some kind of error message).
I am assuming the hang/crash occurs during the running of the code in your post. I have some issues with the code in your post:
1. the Len function which is a text function, is operating on a comparison which will return either TRUE or FALSE either way this will result in the DO loop never being satisfied, and so will be in an endless loop. I think what you probably want is Len(ActiveCell.value)>0 which will become True once an empty cell is found, and so will exit the Do-Loop.
2. there is no function to move the active cell onto the next cell within the normal Do-Loop, so the active cell remains at cell B2 forever, the line ActiveCell.offset(0,1).select should outside of the IF-ENDIF structure but within the DO-LOOP structure i.e. immediately before the LOOP statement.
3. if you have any blank values within a record in your imported file, then the macro will exit (assuming the fixes above are implemented), unless during the import of the file such occurrences are trapped.
Try this code instead:
Do While Len(ActiveCell.Value) > 0
If ActiveCell.Value <> "Ul, Magnitude in kV" Then
Barry Fitzpatrick 20 Feb 2013, 06:48
Please send the file to bazfitzpatrick[at]yahoo.co.uk this is a temporary email address.
Could you also be more specific as to the criteria for the deletion of entries, including the date issue I mentioned in my earlier post.
Please note I can't guarantee results, or the timescale.
Yaseer 20 Feb 2013, 04:49
I am importing text files with a lot of data, I mean 6687 rows and columns right up to "JDV". I only need to extract certain data but I need to do this by deleting some columns.
I have tried the DeleteEntireColumn command but my file always crashes. So I thought the file may be too big so I reduced the number of rows to 8 and still the same thing.
Can u please advise. Heres my code if its necessary:
Do While Len(ActiveCell.Value > 0)
If ActiveCell.Value <> "Ul, Magnitude in kV" Then
HalK 19 Feb 2013, 18:20
Barry, the code you sent me last week doesn't seem to want to function for a second time. Would you be amenable to look at my actual Excel file and advise.
Barry Fitzpatrick 07 Feb 2013, 07:07
The following should do what you want, but there are a number of assumptions regarding the format of the data and the interpretation of dates for instance Excel converts 1-Jan-35 into 1/1/1935 not 1/1/2035. The macro doesn't check if in other the respects the entries except for the date that are identical, as this is not a specified requirement.
Insert this macro on the code page of the Worksheet containing your data. DO keep a backup of your data in case things go wrong.
Dim i As Long
Dim j As Long
i = 1
j = 0
Do While Cells(i, 1) <> ""
Range(Cells(i, 1), Cells(i, 2)).Select
If Cells(i, 1) = Cells(i, 2) Then
If j = 0 Then
j = i
i = i + 1
If DateValue(Mid(Cells(i, 1), 9, 12)) > DateValue(Mid(Cells(j, 1), 9, 12)) Then
i = i + 1
j = 0
i = i + 1
Hal Koberinski 06 Feb 2013, 15:59
I too need to delete rows based on date criteria:
CGLX 66 9-Jan-13
CGLX 67 10-Oct-12
CGLX 67 31-Dec-12
CGLX 71 25-Oct-12
CGLX 72 30-Jan-13
CGLX 74 12-Dec-12
CGLX 74 9-Jan-13
CGLX 75 16-Oct-12
CGLX 76 3-Oct-12
I need to compare records based on columns A and B; if A and B are equal, I then need to delete the row where the date is earlier than the next row where A and B are equal. I have no idea how to use VBA and I am nit a Macro writer, albeit I am an experienced Excel user otherwise. Can any one help?
gncouto 01 Feb 2013, 19:25
Hi, I need to delete rows for which column D value is not "text1" neither "text2". Could you help?
Barry Fitzpatrick 25 Jan 2013, 05:07
From what you describe, it sounds as though you do what you want just using the auto-filtering.
Select the whole of the table containing your data then form the "Data" no the ribbon select "Filter" form the 'Sort & Filter' section.
Use the drop-downs on columns Q and E to select the data that you want. I would then copy the visible rows in to another worksheet for further manipulation.
Wayne 24 Jan 2013, 16:59
i'm trying to create a macro to prepare a spreadsheet for uploading to a web program. i need to sort the sheet by column Q and then delete information from all rows containing information except "Column Q variable" and then sort by column E and delete all remaining rows containing "Column E variable" I have no experience with Visual Basic and would greatly appreciate a more descriptive explanation of what I need to do. Any assistance is greatly appreciated.
Horacio 12 Jan 2013, 08:03
I need to delete rows outside of two time values.
Sample: Rows before 14:15:00 should be deleted as the rows after 16:17:00.
This two time values come from input parameters