Annika has attended:
Excel Forecasting and Data Analysis course
Excel Power Query course
Excel VBA Introduction course
VBA to remove data depending on date - dynamically
Good day,
I would like to ask how to create a VBA that looks removes the rows of data that are not related to the current month, the previous month and the following month. The year is in column XXX and the month is in column YYY displayed as a number (January = 1 etc) rather than text.
I had started to create a table that changes the months and year automatically on a different tab, but couldnt find out how to make it work.
Also, I need a similar (I assume) code that removes all rows for dates that are older than 7 days ago.
currently the dates are in column A.
I hope this is something that you can help me with.
Thanks! Annika
RE: VBA to remove data depending on date - dynamically
Hi Annika,
In my example below I have the year in column e and month number in column f. I assume your data starts from A1.
Please amend this : DateSerial(Cells(iRowCount, "e"), Cells(iRowCount, "f"), 1) to your columns.
Sub RemoveRows()
Dim iRow As Integer
Dim iRowCount As Integer
iRow = Range("a1").CurrentRegion.Rows.Count
For iRowCount = iRow To 2 Step -1
If DateSerial(Cells(iRowCount, "e"), Cells(iRowCount, "f"), 1) <> DateSerial(Year(Date), Month(Date), 1) Then
Cells(iRowCount, "e").EntireRow.Delete
End If
Next iRowCount
End Sub
Older than 7 days (the loop will be the same as above):
If Cells(iRowCount, "a")<date-7 Then
Cells(iRowCount, "e").EntireRow.Delete
End If
Please test it in aa copy of your file
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
