Delete all cells with data from previous dates using VBA

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.

Logic explanation

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 11^th 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 info@exceltip.com