Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Deleting Old Data from a Worksheet.
by Allen Wyatt
(last updated February 11, 2020)
Gene is looking for a way to quickly delete data from a worksheet based on the date in a particular column. If the date is older than today (the date is passed) then the row should be deleted.
This can be rather easily done with a macro. All you need to do is have the macro step through the data and compare the date in each row to today's date. If the date is less than today, then the Delete method is used on the EntireRow object.
Sub DeleteRows1() Dim x As Long Dim iCol As Integer iCol = 7 'Filter all on Col G For x = Cells(Cells.Rows.Count, iCol).End(xlUp).Row To 2 Step -1 If Cells(x, iCol).Value < Date Then Cells(x, iCol).EntireRow.Delete End If Next End Sub
In this example, the macro checks column G (in the iCol variable) for the date. If your date is in a different column, then you should make the change to the variable. Depending on the number of rows of data in your worksheet, the macro may also take quite a while to run.
If you notice a lag in performance, then you may want to use a different approach. The following example uses the AutoFilter capabilities of Excel to first filter the data to show only the old data, and then deletes those rows.
Sub DeleteRows2() Dim Dates As Range Dim nRows As Double Dim currDate As Variant 'Format dates as text Range("Dates").NumberFormat = "@" 'Today's date in number format currDate = CDbl(Date) Range("Dates").AutoFilter Field:=1, _ Criteria1:="<" & currDate nRows = Range("Dates").Rows.Count Rows("2:" & nRows).Select Selection.Delete Shift:=xlUp Range("Dates").AutoFilter Range("Dates").NumberFormat = "m/d/yyyy" Range("C2").Select End Sub
This macro presumes that you have taken the step of assigning a name to your data range. Select all the cells in your data table—including any heading row—and give it the name "Dates." When you run the macro, it uses this range as the target for the AutoFilter.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10783) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Deleting Old Data from a Worksheet.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Want a quick way to jump to the end of your data entry area in a worksheet? The macro in this tip makes quick work of the ...Discover More
Macros are often used to process information in a worksheet. You may need your macro to change the values stored in ...Discover More
When processing information using a macro, you may need to know if there are any other instances of Excel running on a ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.