CODEWITHSUNDEEP
excelvba
Bob Amick
Bob Amick

Reputation: 111

Trying to clear the fill of a range of cells

I'm trying to clear the color pattern that I've put into a range of cells. I ran record a macro to come up with the code. However, now I keep getting an error 1004, select method of range class failed

Listed below is the code I'm using

Sheets("Outputs 2").Range("B19:M24").Select
    Application.CutCopyMode = False
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Any help is greatly appreciated. This forum has helped me tremendously over the past couple of weeks.

Upvotes: 1

Views: 133

Answers (2)

Joshua Dannemann
Joshua Dannemann

Reputation: 2080

This happens sometimes and is nothing unusual. It just means that Excel was unable to set the selection. However, you can always use range references directly like so.

Sub ClearTintAndShade()
    On Error GoTo 1
    Application.CutCopyMode = False
    With ThisWorkbook.Sheets("Outputs 2").Range("B19:M24").Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Exit Sub
1:
    MsgBox ("That sheet does not exist!")
End Sub

Upvotes: 1

Iain Kitching
Iain Kitching

Reputation: 31

Have you renamed your worksheet since you recorded that macro?

Ensure that the name out the worksheet is exactly as it appears in your code.

Upvotes: 1

Related Questions