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.
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.
Learn more about Allen...
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: Deleting Old Data from a Worksheet.
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.
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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!