CODEWITHSUNDEEP
vbaexcel
Shakir Zahid
Shakir Zahid

Reputation: 1

Method union of object global failed for multirange union

I have below code which gives method union object global failed error when it runs. The highlight is showing some issue in set multirange line, tried to amend certain things by still it is giving the same issue. All the ranges are in one worksheet named Input.

Private Sub CommandButton1_Click()

Dim R1, R2, R3, R4, R5, R6, R7, R8, R9, R10, R11, R12, R13, R14, R15, R16, R17, R18, R19, R20, MultiRange1 As Range
Set R1 = Range("I23:Q25" & LR)
Set R2 = Range("D57:O62" & LR)
Set R3 = Range("D65:P70" & LR)
Set R4 = Range("C83:M85" & LR)
Set R5 = Range("O89:Q93" & LR)
Set R6 = Range("D149:Q154" & LR)
Set R7 = Range("D157:Q162" & LR)
Set R8 = Range("D212:H215" & LR)
Set R9 = Range("O212:Q215" & LR)
Set R10 = Range("E219:H230" & LR)
Set R11 = Range("L219:L230" & LR)
Set R12 = Range("N219:N230" & LR)
Set R13 = Range("P219:P230" & LR)
Set R14 = Range("R219:R230" & LR)
Set R15 = Range("C233:Q272" & LR)
Set R17 = Range("E74:K79" & LR)
Set R18 = Range("I8, I11, I13, I15, I17, I39, I172" & LR)
Set R19 = Range("I45, I52, F96, N96, F100, I105, I125, L144, I170" & LR)
Set R20 = Range("I175, I176, I182, G274, C278, E287" & LR)
Set MultiRange1 = Union(R1, R2, R3, R4, R5, R6, R7, R8, R9, R10, R11, R12, R13, R14, R15, R16, R17, R18, R19, R20)
MultiRange1.Select

Upvotes: 0

Views: 401

Answers (1)

Gary's Student
Gary's Student

Reputation: 96773

You need to DIM and assign a value to LR.

Must also individually DIM each of the R's as Range

Also R16 is undefined.

EDIT#1:

Private Sub CommandButton1_Click()
    Dim LR As String

    LR = ""

    Dim R1 As Range, R2 As Range, R3 As Range, R4 As Range, R5 As Range, R6 As Range, R7 As Range, R8 As Range, R9 As Range, R10 As Range, R11 As Range, R12 As Range, R13 As Range, R14 As Range, R15 As Range, R16 As Range, R17 As Range, R18 As Range, R19 As Range, R20 As Range, MultiRange1 As Range

    Set R1 = Range("I23:Q25" & LR)
    Set R2 = Range("D57:O62" & LR)
    Set R3 = Range("D65:P70" & LR)
    Set R4 = Range("C83:M85" & LR)
    Set R5 = Range("O89:Q93" & LR)
    Set R6 = Range("D149:Q154" & LR)
    Set R7 = Range("D157:Q162" & LR)
    Set R8 = Range("D212:H215" & LR)
    Set R9 = Range("O212:Q215" & LR)
    Set R10 = Range("E219:H230" & LR)
    Set R11 = Range("L219:L230" & LR)
    Set R12 = Range("N219:N230" & LR)
    Set R13 = Range("P219:P230" & LR)
    Set R14 = Range("R219:R230" & LR)
    Set R15 = Range("C233:Q272" & LR)
    Set R17 = Range("E74:K79" & LR)
    Set R18 = Range("I8, I11, I13, I15, I17, I39, I172" & LR)
    Set R19 = Range("I45, I52, F96, N96, F100, I105, I125, L144, I170" & LR)
    Set R20 = Range("I175, I176, I182, G274, C278, E287" & LR)

    Set MultiRange1 = Union(R1, R2, R3, R4, R5, R6, R7, R8, R9, R10, R11, R12, R13, R14, R15, R17, R18, R19, R20)
    MultiRange1.Select
End Sub
  1. fixed the DIMs
  2. established LR
  3. removed R16

Upvotes: 1

Related Questions