No Cells Found Error on calling ClearContents in Excel

Automation Monkey 0
Viewed 42

I'm trying to clear all but cells A1 and B1 as they’re my headers whilst retaining formulas in A2, B2 and C1 using the following which is called when a sheet is activated-

Private Sub ClearSelectSheetContents()
    Dim SheetSource As Worksheet
    Set SheetSource = ThisWorkbook.Worksheets("Staff Gantt")
    
    ' clear all cells barring the headers in A and B, also maintain the formulas
    SheetSource.Range("A2:B1000").SpecialCells(xlCellTypeConstants).ClearContents
    SheetSource.Range("C1:XX1000").SpecialCells(xlCellTypeConstants).ClearContents
End Sub

However I am receiving a runtime error "1004" No cells were found. Any ideas on whether there's anything I can do to get around this please?

excel-formula vba excel
1 Answers

My solution:

Private Sub Worksheet_Activate()
Call ClearSelectSheetContents
End Sub

Private Sub ClearSelectSheetContents()
    Dim SheetSource As Worksheet
    Set SheetSource = ThisWorkbook.Worksheets("Staff Gantt")
    
    Dim rngA As Range

    Set rngA = Nothing
    On Error Resume Next
    ' clear all cells barring the headers in A and B, also maintain the formulas
    Set rngA = SheetSource.Range("A2:B1000").SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    
    If Not rngA Is Nothing Then
        rngA.ClearContents
    End If

    Dim rngB As Range

    Set rngB = Nothing
    On Error Resume Next
    ' clear all cells barring the headers in A and B, also maintain the formulas
    Set rngB = SheetSource.Range("C1:XX1000").SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    
    If Not rngA Is Nothing Then
        rngB.ClearContents
    End If
End Sub
Automation Monkey0
Related