vba remove data depending

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » VBA to remove data depending on date - dynamically | Excel forum

VBA to remove data depending on date - dynamically | Excel forum

resolvedResolved · High Priority · Version 365

Annika has attended:
Excel VBA Introduction course
Excel Power Query 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

Tue 28 Nov 2023: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Using Excel's MODE function

Use Excel's MODE function to display the most common value present in a particular range of cells.

The Mode function looks like this:

=MODE(cell range)

As an example, if 35 is the most commonly recurring number in a particular cell range, then the function will display 35.

View all Excel hints and tips


Server loaded in 0.07 secs.