ClearClearedData.bas

Attribute VB_Name = "ClearClearedData"
Option Explicit

' =============================================================================
' Clear Data button macro for the "Cleared Data" worksheet.
'
' INSTALL:
'   1. Import ClearClearedData.bas into Template.xlsm
'   2. On Cleared Data sheet: Developer → Insert → Button (Form Control)
'      Draw the button, assign macro: ClearClearedDataSheet
'      Edit button text to: Clear Data
'   Or run: CreateClearDataButton (once) to place the button automatically
'
' Clears A2 onward (including ItemCode in column A); preserves row 1 headers.
' Does not delete rows/columns, formatting, buttons, or sheet structure.
' Independent of AmazonUrlCleaner and ClearSheet.ClearSheetData.
' =============================================================================

Private Const CLEARED_DATA_SHEET As String = "Cleared Data"
Private Const FIRST_DATA_ROW As Long = 2
Private Const FIRST_DATA_COL As Long = 1

Public Sub ClearClearedDataSheet()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim previousEnableEvents As Boolean
    Dim previousScreenUpdating As Boolean
    Dim answer As VbMsgBoxResult

    On Error GoTo ErrorHandler

    Set ws = GetClearedDataSheet()
    If ws Is Nothing Then
        MsgBox "Worksheet '" & CLEARED_DATA_SHEET & "' was not found.", _
               vbExclamation, "Clear Data"
        Exit Sub
    End If

    answer = MsgBox( _
        "Are you sure you want to clear all data from the Cleared Data sheet?", _
        vbYesNo + vbExclamation, _
        "Clear Data")

    If answer <> vbYes Then Exit Sub

    previousEnableEvents = Application.EnableEvents
    previousScreenUpdating = Application.ScreenUpdating
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Set dataRange = GetClearedDataClearRange(ws)
    If Not dataRange Is Nothing Then
        ' ClearContents keeps formatting, formulas structure, buttons, sheet layout
        dataRange.ClearContents
    End If

    Application.EnableEvents = previousEnableEvents
    Application.ScreenUpdating = previousScreenUpdating

    MsgBox "Cleared Data sheet has been successfully cleared.", _
           vbInformation, "Clear Data"
    Exit Sub

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

' Place / refresh a Form Control button labeled "Clear Data" on Cleared Data.
Public Sub CreateClearDataButton()
    Dim ws As Worksheet
    Dim btn As Button
    Dim existing As Shape
    Dim leftPos As Double
    Dim topPos As Double

    On Error GoTo ErrorHandler

    Set ws = GetClearedDataSheet()
    If ws Is Nothing Then
        MsgBox "Worksheet '" & CLEARED_DATA_SHEET & "' was not found.", _
               vbExclamation, "Clear Data"
        Exit Sub
    End If

    ' Remove previous auto-created button if present
    On Error Resume Next
    For Each existing In ws.Shapes
        If existing.Type = msoFormControl Then
            If StrComp(existing.OnAction, "ClearClearedDataSheet", vbTextCompare) = 0 Or _
               StrComp(existing.Name, "btnClearClearedData", vbTextCompare) = 0 Then
                existing.Delete
            End If
        End If
    Next existing
    On Error GoTo ErrorHandler

    leftPos = ws.Range("L1").Left
    topPos = ws.Range("L1").Top

    Set btn = ws.Buttons.Add(leftPos, topPos, 100, 28)
    With btn
        .Name = "btnClearClearedData"
        .OnAction = "ClearClearedDataSheet"
        .Characters.Text = "Clear Data"
    End With

    MsgBox "Clear Data button added to the Cleared Data sheet.", _
           vbInformation, "Clear Data"
    Exit Sub

ErrorHandler:
    MsgBox "Could not create the Clear Data button." & vbCrLf & _
           Err.Description, vbCritical, "Clear Data"
End Sub

Private Function GetClearedDataSheet() As Worksheet
    On Error Resume Next
    Set GetClearedDataSheet = ThisWorkbook.Worksheets(CLEARED_DATA_SHEET)
    On Error GoTo 0
End Function

' Data area: A2 through last used cell (keeps row 1 headers only).
Private Function GetClearedDataClearRange(ByVal ws As Worksheet) As Range
    Dim lastRow As Long
    Dim lastCol As Long
    Dim colIndex As Long
    Dim colLastRow As Long
    Dim used As Range

    On Error Resume Next
    Set used = ws.UsedRange
    On Error GoTo 0

    If used Is Nothing Then Exit Function

    lastRow = FIRST_DATA_ROW - 1
    lastCol = used.Column + used.Columns.Count - 1
    If lastCol < FIRST_DATA_COL Then Exit Function

    For colIndex = FIRST_DATA_COL To lastCol
        colLastRow = ws.Cells(ws.Rows.Count, colIndex).End(xlUp).Row
        If colLastRow > lastRow Then lastRow = colLastRow
    Next colIndex

    If lastRow < FIRST_DATA_ROW Then Exit Function

    Set GetClearedDataClearRange = ws.Range( _
        ws.Cells(FIRST_DATA_ROW, FIRST_DATA_COL), _
        ws.Cells(lastRow, lastCol))
End Function