Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Conditionally Deleting Rows.

Conditionally Deleting Rows

by Allen Wyatt
(last updated September 11, 2015)

18

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:

Sub DeleteRows()
    Dim strToDelete As String
    Dim rngSrc As Range
    Dim NumRows As Integer
    Dim ThisRow As Integer
    Dim ThatRow As Integer
    Dim ThisCol As Integer
    Dim J As Integer
    Dim DeletedRows As Integer

    strToDelete = InputBox("Value to Trigger Delete?", "Delete Rows")
    Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)

    NumRows = rngSrc.Rows.Count
    ThisRow = rngSrc.Row
    ThatRow = ThisRow + NumRows - 1
    ThisCol = rngSrc.Column

    For J = ThatRow To ThisRow Step -1
        If Cells(J, ThisCol) = strToDelete Then
            Rows(J).Select
            Selection.Delete Shift:=xlUp
            DeletedRows = DeletedRows + 1
        End If
    Next J
    MsgBox "Number of deleted rows: " & DeletedRows
End Sub

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:

http://www.ozgrid.com/VBA/VBACode.htm

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2386) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Conditionally Deleting Rows.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Saving Everything

Need to force users to save their work? It may be as simple as implementing a couple of macros that get a bit more aggressive ...

Discover More

Removing Hyperlinks without a Macro

If you have a whole slew of hyperlinks in a worksheet and you want to get rid of them, it's easier than you think. This ...

Discover More

Relative References within Named Ranges

Excel is usually more flexible in what you can reference in formulas than is immediately apparent. This tip examines some ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (menu)

Stopping the Deletion of Cells

You can delete cells from a worksheet, and Excel will move the remaining cells either to the left or upwards. Deletions, ...

Discover More

Quickly Deleting Rows and Columns

Deleting rows or columns is easy when you use the shortcut described in this tip. Just select the rows or columns and then ...

Discover More

Deleting Blank Columns

Import data from another program, and you could end up with a lot of blank columns in your data. Here's the quickest way to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three more than 8?

2017-04-05 23:46:55

Aireena Khairul

after highlight the row in excel, one row has been deleted so how to maintaining the rest of the data below the row that has been deleted


2016-09-19 13:24:43

Eric

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?


2016-07-21 10:19:49

Ronan

Hi,

can the above code be amended to delete rows greater than a particular date?


2015-11-10 13:36:58

Lary

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?


2015-10-12 01:24:41

ASLAM

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


2014-10-20 22:40:53

Matt

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.


2014-06-02 12:00:48

Franklin

Hello Allen,

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.

Thank you,

Franklin


2013-04-02 20:17:49

brett

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.


2013-02-20 07:23:08

Barry Fitzpatrick

Yaseer,

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:

Range("B2").Select
Do While Len(ActiveCell.Value) > 0

If ActiveCell.Value <> "Ul, Magnitude in kV" Then
ActiveCell.EntireColumn.Delete
End If

ActiveCell.Offset(0, 1).Select
Loop


2013-02-20 06:48:13

Barry Fitzpatrick

Hal,

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.


2013-02-20 04:49:21

Yaseer

Hi Barry

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:
Range("B2").Select
Do While Len(ActiveCell.Value > 0)

If ActiveCell.Value <> "Ul, Magnitude in kV" Then
ActiveCell.EntireColumn.Delete
ActiveCell.Offset(0, 1).Select
End If
Loop

Regards


2013-02-19 18:20:43

HalK

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.


2013-02-07 07:07:32

Barry Fitzpatrick

Hal,

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.

Sub DeleteRows()
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
Else
If DateValue(Mid(Cells(i, 1), 9, 12)) > DateValue(Mid(Cells(j, 1), 9, 12)) Then
Cells(j, 1).EntireRow.Delete
i = i + 1
Else
Cells(i, 1).EntireRow.Delete
End If
j = 0
End If
Else
i = i + 1
End If
Loop

End Sub


2013-02-06 15:59:43

Hal Koberinski

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?


2013-02-01 19:25:19

gncouto

Hi, I need to delete rows for which column D value is not "text1" neither "text2". Could you help?


2013-01-25 05:07:52

Barry Fitzpatrick

Wayne,
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.


2013-01-24 16:59:45

Wayne

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.


2013-01-12 08:03:55

Horacio

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


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.