Excel VBA selecting multiple dynamic ranges

dynamic, excel, range, union, vba

Solution

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.

Problem

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 ```

Original source