Excel.Tips.Net ExcelTips (Menu Interface)

Conditionally Deleting Rows

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.

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
            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:


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.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

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

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
         End If

         ActiveCell.Offset(0, 1).Select
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
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:
   Do While Len(ActiveCell.Value > 0)
        If ActiveCell.Value <> "Ul, Magnitude in kV" Then
            ActiveCell.Offset(0, 1).Select
            End If

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.

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

End Sub
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

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.