Excel VBA to select multiple dynamic ranges

Question

I am trying to select multiple dynamic ranges. I try to use the join method, and I get the 'Range' of 'object' Global Failed error method in the first line of Set.

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 

Answer 1

The problem is due to the comma in the range operators. That is, when you install R1 , you should write:

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

In addition, when you determine the object type of your variables R1 , ..., R5 , you must 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 an option. This does not cause a problem, but it will save memory and make cleaner code.

Answer 2

You can also install it like this:

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

What you did was that you mix Range syntax. The following is the general syntax for Range:

Using : to determine Range :

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

Using , to determine Range :

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

Using the Cells property:

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

Using the 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 has certain advantages and benefits.
Use the most appropriate syntax for you.

Additionally:

This one uses the Intersect Function :

 Set R1 = Intersect(Columns("A:A"),Rows("1:" & LR)) 

Answer 3

What should I do if I need to select all ranges, but these ranges will be determined only after a series of checks performed by the macro. How would you define several ranges if we do not yet know how many there will be? And then what to do to select all of them at once? Thank you