In this article, we will create a macro to delete data of previous dates. This macro checks the data, keeps today’s data and deletes all the data from previous dates.
Raw data in this example consists of date, agent name and sales of the individual agent on that particular date.
To run the macro, click the button on the “Main” sheet or one can even run it by pressing shortcut keys Alt + F8.
Macro will delete all the records with previous dates. As we run this macro on 14 December, 2016, the output will reflect records only for that date.
In this example, we have created “RemovePreviousData” macro. This macro uses reverse FOR loop, means loop runs in opposite direction. Loop starts from the last row and moves towards the top row.
Reason for using reverse looping is that if we had used normal FOR loop, then after each row deletion, records present in the data would have shifted upwards, resulting in change of positioning with each row deletion. So to counter that, we have used reverse loop.
We have used Step statement along with FOR loop for looping in the reverse direction. FOR loop begins from the last row and loops towards 11th row.
Please follow below for the code
Option Explicit Sub RemovePreviousData() Dim Counter, LastRow As Integer 'Finding the row number of last row LastRow = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row 'Looping from last row to 11th row For Counter = LastRow To 11 Step -1 If Cells(Counter, 1).Value < Date Then 'Deleting the row Rows(Counter).Delete End If Next Counter End Sub
If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.