Automate data archiving and purging processes, Excel VBA

Here’s an example of a VBA code to automate the archiving and purging process of data in Excel. The process will move data older than 6 months to an « Archive » sheet and delete the old data from the original sheet.

Scenario:

You have an Excel sheet where column A contains dates, and you want to archive rows where the date is older than 6 months and then delete those rows from the source sheet.

Example VBA Code

1. Archive Old Data: Move rows with dates older than 6 months to another sheet called Archive.
2. Purge Old Data: Delete rows older than 6 months in the source sheet.

Here’s the VBA code:

Sub ArchiveAndPurgeData()
    Dim wsSource As Worksheet
    Dim wsArchive As Worksheet
    Dim lastRow As Long
    Dim archiveRow As Long
    Dim currentDate As Date
    Dim cutoffDate As Date
    Dim i As Long
    ' Set the source worksheet (the one containing data to process)
    Set wsSource = ThisWorkbook.Sheets("Data")  ' Change the name of the sheet as needed
    ' Set the archive worksheet
    Set wsArchive = ThisWorkbook.Sheets("Archive")  ' Change the name of the archive sheet as needed
    ' Get the last row with data in the source sheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    ' Get the current date and calculate the cutoff date (6 months ago)
    currentDate = Date
    cutoffDate = DateAdd("m", -6, currentDate)  ' 6 months before today
    ' Initialize the archive row counter
    archiveRow = wsArchive.Cells(wsArchive.Rows.Count, "A").End(xlUp).Row + 1
    ' Loop through all data rows in the source sheet
    For i = lastRow To 2 Step -1 ' Start from the last row and move upwards
        ' Check if the value in column A is a valid date
        If IsDate(wsSource.Cells(i, 1).Value) Then
            ' Check if the date in column A is older than the cutoff date
            If wsSource.Cells(i, 1).Value < cutoffDate Then
                ' Copy the row to the archive sheet
                wsSource.Rows(i).Copy wsArchive.Rows(archiveRow)
                archiveRow = archiveRow + 1
                ' Delete the row from the source sheet
                wsSource.Rows(i).Delete
            End If
        End If
    Next i
    MsgBox "Data archiving and purging complete.", vbInformation
End Sub

Explanation of the Code:

1. Setting Worksheets:
   • wsSource is the worksheet containing the data to be processed (e.g., « Data »).
   • wsArchive is the worksheet where old data will be archived (e.g., « Archive »).
2. Calculating the Cutoff Date:
   • currentDate holds the current date.
   • cutoffDate is the date 6 months ago, which will be used to determine which data is « old ».
3. Loop Through Data:
   • The For loop goes from the last row of the data down to the second row (Step -1), which ensures that deleting rows doesn’t affect the remaining data.
4. Archiving and Deleting Data:
   • If the date in column A is older than cutoffDate, that row is copied to the Archive sheet.
   • After the row is copied, it is deleted from the source sheet.
5. Completion Message:
   • After the loop finishes, a message box will appear, informing the user that the archiving and purging are complete.

Things to Customize:

• Sheet Names: Ensure that the sheet names (« Data » and « Archive ») match the actual names in your workbook.
• Date Column: The code assumes the dates are in column A. If your dates are in another column, adjust the column reference accordingly.
• Data Range: If your data is not just in column A but spans other columns, adjust the code to handle the range you need.

Precautions:

• Backup Your Data: Before running the script, make sure to back up your workbook, as deleted rows cannot be recovered after the macro runs.
• Test with Sample Data: Test the macro with a small data set to ensure it behaves as expected.

This script helps automate the process of archiving and purging old data in Excel, especially useful when managing large datasets and ensuring that only relevant data remains in your working sheet.