Forum Discussion
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
- mathetesSilver 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.
- NikolinoDEPlatinum 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