Select method of range class failed for selection

Question

Hi i have recorded a macro and i want to implement it into my current vba macro, however upon testing the code with the button on another sheet, there seem to be an

error 1004 which states Select method of range class failed

highlighting the selection part of the code." Sheets("Raw").Cells.Select " did i miss out anything in my code or is there any suggestion. Thanks in advance !

Private Sub CommandButton1_Click()
' unmerge Macro
' Keyboard Shortcut: Ctrl+q

Sheets("Raw").Cells.Select
Selection.unmerge
    With Selection
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        End With

    Sheets("Raw").Rows("1:5").Select
    Selection.Delete Shift:=xlUp
    Sheets("Raw").Cells.Select
    Selection.ColumnWidth = 8.29
    Sheets("Raw").Range("C:C,E:F,H:H,J:M,O:R,T:T,V:W,Y:AA").Select
    Sheets("Raw").Range("Y1").Activate

    Sheets("Raw").Range( _
        "C:C,E:F,H:H,J:M,O:R,T:T,V:W,Y:AA,AC:AD,AF:AH,AJ:AJ,AL:AM,AO:AO,AQ:AR,AT:AU"). _
        Select
    Sheets("Raw").Range("AT1").Activate

    Sheets("Raw").Range( _
        "C:C,E:F,H:H,J:M,O:R,T:T,V:W,Y:AA,AC:AD,AF:AH,AJ:AJ,AL:AM,AO:AO,AQ:AR,AT:AU,AW:AY" _
        ).Select
   Sheets("Raw").Range("AW1").Activate
    Selection.Delete Shift:=xlToLeft
    Sheets("Raw").Range("AG23").Select

    Sheets("Raw").Cells.Select
    Selection.ColumnWidth = 9.71
    Selection.ColumnWidth = 13.71
    Sheets("Raw").Range("D14").Select
End Sub

Have you seen this poast before? If not have a look before doing anymore coding... https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1 — alowflyingpig, Dec 19 '19 at 05:44

score 1 · Accepted Answer · answered Dec 19 '19 at 05:44

1

If you are using .Select on cells then the sheet of those cells must be active. Add this on the first line:

Sheets("Raw").Activate

answered Dec 19 '19 at 05:44

Adrian Celis

193
5

1

Using `.Select` and `.Activate` is frowned upon and also makes any code messy, unreadable and very slow... Suggest OP reads my link as this will have many benefits now and in the future – alowflyingpig Dec 19 '19 at 05:45
it works thanks! but ill try to change according to the link. – programmer Dec 19 '19 at 08:13

Select method of range class failed for selection

1 Answers1