Spaces:
Sleeping
Sleeping
File size: 3,202 Bytes
38fdd3d | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 | 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
|