4

I'm trying to selecting multiple dynamic range. Trying to use the union method and I'm getting Method 'Range' of 'object' Global Failed error on first Set line.

Dim LR As Long
LR = Range("A60000").End(xlUp).Row

Dim R1, R2, R3, R4, R5, MultiRange As Range
Set R1 = Range("A7,:A" & LR)
Set R2 = Range("D7,:D" & LR)
Set R3 = Range("G7,:G" & LR)
Set R4 = Range("H7,:H" & LR)
Set R5 = Range("J7,:J" & LR)
Set MultiRange = Union(R1, R2, R3, R4, R5)
MultiRange.Select
Selection.Copy
double-beep
  • 5,031
  • 17
  • 33
  • 41
Hinson.Li
  • 43
  • 1
  • 1
  • 4

3 Answers3

7

The problem occurs because of the comma in you range statements. I.e. when you set R1 you should write:

Set R1 = Range("A7:A" & LR)

Also, when you define the object type of your variables R1, ..., R5 you should write it as

Dim R1 As Range, R2 As Range, R3 As Range, R4 As Range, R5 As Range, MultiRange As Range

Otherwise R1, ..., R5 will be defined as a Variant. This doesn't cause a problem, but it will save memory and makes for a cleaner code.

Netloh
  • 4,338
  • 4
  • 25
  • 38
5

You can also set it like this:

Set R1 = Range("A7","A" & LR)

What you did is you kinda mixed up the Range syntax.
See below some common Range Syntax:

Using : to define Range:

Range("A1:A" & LR) '~~> where LR holds the last row number

Using , to define Range:

Range("A1","A" & LR)

Using Cells property:

Range(Cells(1, "A"),Cells(LR, "A"))
Range(Cells(1, 1),Cells(LR, 1)) '~~> another way

Using Range property:

Range(Range("A1"),Range("A" & LR))
Range(Range("A1").address & ":" & Range("A" & LR).Address) '~~> yet another complicated way

All syntax above evaluates to: $A$1:$A$(LR)
Each have certain advantages and uses.
Use the syntax you're most comfortable with.

Additional:

This one uses Intersect Function:

Set R1 = Intersect(Columns("A:A"),Rows("1:" & LR))
L42
  • 19,427
  • 11
  • 44
  • 68
0

I think the issue with your code is that you put comma "," after A7, D7, ..... "Set R1 = Range("A7,:A" & LR)" Try this code:

Sub JubaTest()

Dim LR As Long
Dim R1, R2, R3, R4, R5, MultiRange As Range

LR = Range("A60000").End(xlUp).Row

Set R1 = Range("A7:A" & LR)
Set R2 = Range("D7:D" & LR)
Set R3 = Range("G7:G" & LR)
Set R4 = Range("H7:H" & LR)
Set R5 = Range("J7:J" & LR)
Set MultiRange = Union(R1, R2, R3, R4, R5)
MultiRange.Select

End Sub
Pierre.Vriens
  • 2,117
  • 75
  • 29
  • 42
Ahmed Berkane
  • 1
  • Think the main issue has been answered already in the accepted post which you should read carefully as you are repeating the initial declaration error `Dim R1, R2, .. As Range` instead of `Dim R1 As Range, R2 As Range, R3 As Range, ...` @AhmedBerkane – T.M. Sep 10 '21 at 18:22