ClearSheet.bas

Attribute VB_Name = "ClearSheet"
Option Explicit

' Clear Sheet: archive data from row 2+, clear contents, remove duplicate highlights.
' No success popup; only show a message if an error occurs.
Public Sub ClearSheetData()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim dataRange As Range, dataArr As Variant
    Dim archiveWs As Worksheet, archiveLast As Long
    Dim c As Range
    Dim i As Long
    Dim previousEnableEvents As Boolean

    On Error GoTo ErrorHandler

    Set ws = ActiveSheet
    previousEnableEvents = Application.EnableEvents
    Application.EnableEvents = False

    On Error Resume Next
    Set c = ws.Cells.Find( _
        What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious _
    )
    On Error GoTo ErrorHandler

    If c Is Nothing Then
        ClearDuplicateHighlights ws
        GoTo Cleanup
    End If

    lastRow = c.Row
    If lastRow <= 1 Then
        ClearDuplicateHighlights ws
        GoTo Cleanup
    End If

    On Error Resume Next
    Set c = ws.Cells.Find( _
        What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious _
    )
    On Error GoTo ErrorHandler

    If c Is Nothing Then
        lastCol = 1
    Else
        lastCol = c.Column
    End If

    Set dataRange = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, lastCol))
    dataArr = dataRange.Value

    ReDim Preserve dataArr(1 To UBound(dataArr, 1), 1 To lastCol + 1)
    For i = 1 To UBound(dataArr, 1)
        dataArr(i, lastCol + 1) = Now
    Next i

    On Error Resume Next
    Set archiveWs = ThisWorkbook.Sheets("Cleared Data")
    On Error GoTo ErrorHandler

    If archiveWs Is Nothing Then
        Set archiveWs = ThisWorkbook.Sheets.Add(After:= _
                          ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        archiveWs.Name = "Cleared Data"
    End If

    If Application.WorksheetFunction.CountA(archiveWs.Cells) = 0 Then
        archiveLast = 0
    Else
        archiveLast = archiveWs.Cells.Find(What:="*", _
                         After:=archiveWs.Cells(1, 1), _
                         SearchOrder:=xlByRows, _
                         SearchDirection:=xlPrevious).Row
    End If

    If archiveLast = 0 Then
        For i = 1 To lastCol
            archiveWs.Cells(1, i).Value = ws.Cells(1, i).Value
        Next i
        archiveWs.Cells(1, lastCol + 1).Value = "Cleared Timestamp"
        archiveLast = 1
    End If

    archiveWs.Range( _
      archiveWs.Cells(archiveLast + 1, 1), _
      archiveWs.Cells(archiveLast + UBound(dataArr, 1), lastCol + 1) _
    ).Value = dataArr

    ' Clear contents only — preserves layout, borders, fonts, and other formatting.
    dataRange.ClearContents
    ClearDuplicateHighlights ws

Cleanup:
    Application.EnableEvents = previousEnableEvents
    Exit Sub

ErrorHandler:
    Application.EnableEvents = previousEnableEvents
    MsgBox "Clear Sheet could not be completed." & vbCrLf & _
           Err.Description, vbCritical, "Clear Sheet"
End Sub