Excel VBA selecting multiple dynamic ranges

问题:

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

Could anyone help?

Thanks

回答1:

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.

回答2:

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