CODEWITHSUNDEEP
excelvba
Alan Tingey
Alan Tingey

Reputation: 971

Excel - Multiple Cell Ranges error 1004 when using VBA

I have one line of code that selects multiple cell ranges as follows:

Range("B9:M9,B12:M12,B15:M15,B18:M18,B21:M21,B24:M24,B27:M27,B30:M30,B33:M33,R9:AC9,R12:AC12,R15:AC15,R18:AC18,R21:AC21,R24:AC24,R27:AC27,R30:AC30,R33:AC33,AH9:AS9,AH12:AS12,AH15:AS15,AH18:AS18,AH21:AS21,AH24:AS24,AH27:AS27,AH30:AS30,AH33:AS33,AW9:BI9,AW12:BI12,AW15:BI15").Select

However, it looks like I am selecting too many cells as I get the error in the picture at the bottom of the question (error 1004). If I remove some of the selection it works.

I decided to record a macro and see how it copes and it did the following code:

    Union(Range( _
    "AW24:BI24,AW27:BI27,AW30:BI30,AW33:BI33,B9:M9,B12:M12,B15:M15,B18:M18,B21:M21,B24:M24,B27:M27,B30:M30,B33:M33,R9:AC9,R12:AC12,R15:AC15,R18:AC18,R21:AC21,R24:AC24,R27:AC27,R30:AC30,R33:AC33,AH9:AS9,AH12:AS12,AH15:AS15,AH18:AS18,AH21:AS21,AH24:AS24,AH27:AS27" _
    ), Range("AW12:BI12,AW15:BI15,AW18:BI18,AW21:BI21")).Select

However, when I try to run the code again I get the same 1004 error. Any help greatly appreciated

enter image description here

Full Code Below:

Sub updateSelectedMetric()

currentChosenMetric = Range("A4").Value

Range("B9:M9,B12:M12,B15:M15,B18:M18,B21:M21,B24:M24,B27:M27,B30:M30,B33:M33,R9:AC9,R12:AC12,R15:AC15,R18:AC18,R21:AC21,R24:AC24,R27:AC27,R30:AC30,R33:AC33,AH9:AS9,AH12:AS12,AH15:AS15,AH18:AS18,AH21:AS21,AH24:AS24,AH27:AS27,AH30:AS30,AH33:AS33").Select

'Union(Range( _
'    "AW24:BI24,AW27:BI27,AW30:BI30,AW33:BI33,B9:M9,B12:M12,B15:M15,B18:M18,B21:M21,B24:M24,B27:M27,B30:M30,B33:M33,R9:AC9,R12:AC12,R15:AC15,R18:AC18,R21:AC21,R24:AC24,R27:AC27,R30:AC30,R33:AC33,AH9:AS9,AH12:AS12,AH15:AS15,AH18:AS18,AH21:AS21,AH24:AS24,AH27:AS27" _
'    ), Range("AW12:BI12,AW15:BI15,AW18:BI18,AW21:BI21")).Select

Select Case currentChosenMetric
    Case "Waste (%)", "CiCL <1", "DSODA", "DCODA"
        Selection.NumberFormat = "0.0%"
    Case "UPT", "Sales (U)", "CTF (U)"
        Selection.NumberFormat = "#,##0"
    Case "RoS", "CTF to Sales"
        Selection.NumberFormat = "##.0"
    Case "Sales (£)", "Waste (£)"
        Selection.NumberFormat = "£#,##0"
End Select

Range("A1").Select

End Sub

Upvotes: 0

Views: 141

Answers (1)

Rui Manso
Rui Manso

Reputation: 54

Splitting the selection seems to work...

    Range("R27:AC27,R30:AC30,R33:AC33,AH9:AS9,AH12:AS12,AH15:AS15,AH18:AS18,AH21:AS21,AH24:AS24,AH27:AS27,AH30:AS30,AH33:AS33,AW9:BI9,AW12:BI12,AW15:BI15").Select
Union(Selection, Range("B9:M9,B12:M12,B15:M15,B18:M18,B21:M21,B24:M24,B27:M27,B30:M30,B33:M33,R9:AC9,R12:AC12,R15:AC15,R18:AC18,R21:AC21,R24:AC24")).Select

Upvotes: 1

Related Questions