Deleting Rows before a Cutoff Date

Written by Allen Wyatt (last updated January 31, 2022)
This tip applies to Excel 2007, 2010, 2013, and 2016


5

Barry has a large worksheet containing several thousand rows of data. Column B contains a date, and he needs to delete all the rows in which the date in column B is earlier than a specific cutoff date. Barry wonders about the easiest way to do this for so much data.

This is rather easy to do, with the approach you use dependent on how often you need to do it and how you want to work with your data. If you don't care what order your data is in, then the easiest method is what I refer to as the "sort and delete" method:

  1. Select cell B2. (This assumes that B2 is the first date in your rows of data because row 1 contains headers.)
  2. Display the Data tab of the ribbon.
  3. Click the Sort Oldest to Newest tool. Excel sorts the data according to the dates in column B, with the oldest date in row 2.
  4. Select and delete the rows that contain dates before your cutoff.

This works great if you only need to perform that task once in a while and if you don't mind the rows in the data being reordered. If reordering is a problem, then you may want to add a column to your data and fill that column with values from 1 to however many rows of data you have. You can then perform the "sort and delete" method, but afterwards resort your data based on the values in the column you added.

Of course, you could also use a "filter and delete" method, which will leave your data in its original order without the need of a helper column:

  1. Select cell B2. (This assumes that B2 is the first date in your rows of data because row 1 contains headers.)
  2. Press Ctrl+Shift+L. Excel applies AutoFilter to your data. (You should be able to see the small drop-down arrows next to the headers in row 1.)
  3. Click the drop-down arrow next to the Date header in cell B1. Excel displays some sorting and filtering options.
  4. Hover your mouse pointer over the Date Filters option. Excel displays even more options.
  5. Choose the Before option. Excel displays the Custom AutoFilter dialog box.
  6. In the box to the right of "Is Before," specify a date one day after your cutoff date.
  7. Click OK. Excel applies the filter and you can only see those rows that are at or before your cutoff date.
  8. Select all the rows, but not row 1. (That's because row 1 contains your headers.)
  9. Display the Home tab of the ribbon.
  10. Click the Delete tool. Excel deletes all the selected rows.
  11. Display the Data tab of the ribbon.
  12. Click the Filter tool to remove the AutoFilter.

If you need to perform the task of removing rows often, then you won't be able to beat the convenience of using a macro. The following macro assumes that you've placed the cutoff date into cell K1. It grabs this date and then looks at each row in your data, deleting any rows that are before this cutoff date.

Sub DeleteRowsBeforeCutoff()
    Dim LastRow As Integer
    Dim J As Integer

    Application.ScreenUpdating = False
    LastRow = Cells(Rows.Count, 2).End(xlUp).Row
    For J = LastRow To 1 Step -1
        If Cells(J, 2) < [K1] Then
            Cells(J, 2).EntireRow.Delete
        End If
    Next J
    Application.ScreenUpdating = True
End Sub

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1566) applies to Microsoft Excel 2007, 2010, 2013, and 2016.

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

Changing the Footnote Continuation Notice

When a footnote needs to span two printed pages, Word prints a continuation notice at the end of the footnote being ...

Discover More

Printing Only Changed Pages

Turn Track Changes on, and you can easily see where you've made changes throughout a document. If you want to print only ...

Discover More

Decreasing a Paragraph's Indent

When formatting your document, you often have a need to indent paragraphs. If you later want to decrease the indent used ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Deleting Stubborn Links

Deleting unwanted links in a workbook can be a challenge, particularly if you are not sure how those links got there. ...

Discover More

Deleting All Names but a Few

Want to get rid of most of the names defined in your workbook? You can either delete them one by one or use the handy ...

Discover More

Viewing Your Work Full-Screen

Want to use the maximum space possible for displaying information on screen? You'll want to learn how to use the ...

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}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. 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 five more than 6?

2022-02-15 11:58:58

J. Woolley

For a similar Tip, see
https://excelribbon.tips.net/T010783_Deleting_Old_Data_from_a_Worksheet.html


2022-02-05 18:36:46

Peter

Oops
Columns(2) should be Columns(1) in my example (where the field being tested is in column A). Just need to make sure $A$ matches (1)
Peter


2022-02-05 03:11:15

Peter

If you try to automate the "filter and delete" method, you would use something like the following.

The resulting visible rows will not be contiguous so you have to work with areas. This is not a problem - you just cycle through and delete all the rows in each area. Area 1 is the column heading and is not deleted.

Columns(2).AutoFilter Field:=1, Criteria1:="<0"
For nA = nAreas To 2 step -1 ' delete areas from the bottom.
Set aa = Columns(1).SpecialCells(xlCellTypeVisible).Areas(nA) ' next area to delete
Set rr = Rows(Replace(aa.Address, "$A$", ""))
rr.Delete Shift:=xlUp
Next nA
Columns(1).AutoFilter


2021-07-08 16:35:43

Philip

One note of caution with the macro approach : be aware that “undo” won’t be possible after executing the macro …


2021-01-01 23:39:57

dremmarket

"What’s up it’s me, I am also visiting this web page daily,
this site is in fact pleasant and the users are
genuinely sharing pleasant thoughts."
Regards: <a href="https://dreammarket.link">Dream market</a>


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu 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.