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
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.
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))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With