Written by Allen Wyatt (last updated June 11, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
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 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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3384) 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: Deleting Old Data from a Worksheet.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
When editing worksheets, it is important to know how to add rows and columns. Excel provides a couple of quick ways you ...
Discover MoreEnter information into a cell, and Excel needs to figure out what type of information it is. Here's how Excel interprets ...
Discover MoreInsert a symbol into a cell, and it should stay there, right? What if the symbol changes to another character, such as a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments