CODEWITHSUNDEEP
excelrangevba
Greg
Greg

Reputation: 43

Error selecting cell range

When attempting to clear a cell range in a hidden worksheet I am receiving a "Select method of range class failed error" on line .Range("A1:EC168").select

Below is a copy of my code, thanks for any advice.

Private Sub ClearAll_Click()
Dim Sheet As Worksheet
Dim CompareTool As Workbook
Dim Sheetname As String
Set CompareTool = ThisWorkbook

With Application
    .DisplayAlerts = False
    .ScreenUpdating = True
End With

For Each Sheet In CompareTool.Worksheets
    If Left(Sheet.Name, 8) = "Scenario" Then
    Sheetname = Sheet.Name
        With CompareTool.Sheets(Sheetname)
        .Visible = True
        .Range("A1:EC168").Select
        .Visible = False
        End With
    End If
Next Sheet

Unload Me
End Sub

Upvotes: 0

Views: 424

Answers (1)

David Zemens
David Zemens

Reputation: 53623

You will not be able to Select anything on an inactive sheet, so the solution would be to Activate it prior to the Select statement, though since the sheet is hidden, I'm not sure what the benefit of making the selection is...

    With Sheet
    .Visible = True
    .Activate
    .Range("A1:EC168").Select
    .Visible = False
    End With

You don't need to select the range to delete it, just do .Range("A1:EC168").Delete. This way, you don't even need to activate or make it visible:

    With Sheet
    .Range("A1:EC168").Delete
    End With

Upvotes: 3

Related Questions