Forum Discussion

kyavari's avatar
kyavari
Occasional Reader
Nov 13, 2025

Cleaning up data with Macros

Hello everyone! I am cleaning up a worksheet that I use frequently to track weekly expenses and spending. I just discovered Macros and I want to use them to clean up my worksheet. Using Macros, how can I create a button that clears entries that are 3 (or more) months old? 

2 Replies

  • mathetes's avatar
    mathetes
    Silver Contributor

    NikolinoDE​ gave you the macro you requested.

    I want to question your original request. To me--the creator and owner of a spreadsheet that I too use to track income and expenses--your request doesn't make complete sense. If you really are interested in tracking such things, your interest should extend (I would think) to comparing expenses on X in this year with expenses on X in the past year; or quarter to quarter, or ...whatever. The point being, Excel has plenty of capacity to hold data on transactions for many years. I can't understand--which is why I'm asking--why you would want to limit your tracking to only the last three months. 

    Are you just wanting to make sure each check or credit card charge is accounted for? That could be a legitimate goal, though it wouldn't really qualify as "tracking" in my book. "Tracking," to me, implies an interest in longer term attention to trends in spending in various categories.

    So my purpose in asking this is to invite you to a deeper possible goal in your use of Excel.

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor

    Below is a simple, reliable way to create a macro-powered button that clears rows older than 3 months in Excel.

    Sub ClearOldEntries()
    
        Dim ws As Worksheet
        Dim lastRow As Long
        Dim checkDate As Date
        Dim i As Long
        
        ' Change to your sheet name if needed
        Set ws = ThisWorkbook.Sheets("Sheet1")
        
        ' Date threshold: 3 months before today
        checkDate = DateAdd("m", -3, Date)
        
        ' Find last used row
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        
        ' Loop from bottom to top to avoid skipping rows
        For i = lastRow To 2 Step -1   ' assumes row 1 is headers
            If IsDate(ws.Cells(i, "A").Value) Then
                If ws.Cells(i, "A").Value < checkDate Then
                    ws.Rows(i).ClearContents    ' clear contents only
                    ' or use: ws.Rows(i).Delete   — if you want to delete the entire row
                End If
            End If
        Next i
    
    End Sub

     

Resources