Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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 August 9, 2019)
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, and 2013. You can find a version of this tip for the older menu interface of Excel here: Deleting Old Data from a Worksheet.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Macros are often used to process information in a workbook. If your macro makes changes in what is selected in the ...Discover More
Excel allows you to define names that can refer either to ranges of cells or to constant information, such as formulas. ...Discover More
Excel keeps track of the actions you take so that you can undo those actions if any are taken in error. You may want to ...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.