Excel VBA选择多个动态范围 [英] Excel VBA selecting multiple dynamic ranges

问题描述

我正在尝试选择多个动态范围。尝试使用联合方法，我得到第一个设置行的对象全局失败错误的Method'Range'。

  Dim LR As Long 
 LR = Range（A60000）。End（xlUp）.Row 
 
 Dim R1，R2，R3，R4，R5，MultiRange As Range 
设置R1 =范围（A7，：A& LR）
设置R2 =范围（D7，：D& LR）
设置R3 =范围（G7，：G& ; LR）
设置R4 =范围（H7，H& LR）
设置R5 =范围（J7，：J& LR）
设置MultiRange = R1，R2，R3，R4，R5）
 MultiRange.Select 
 Selection.Copy 
  

有人可以帮忙吗？

谢谢

解决方案

由于您的逗号范围陈述。即当您设置 R1 时，您应该写：

 设置R1 =范围A7：A& LR）
  

此外，当您定义变量的对象类型 R1 ，...， R5 您应该将其写为

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

否则 R1 ，...， R5 将定义为变式。这不会造成问题，但它会节省内存，并使更清洁的代码。

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

解决方案

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.

这篇关于Excel VBA选择多个动态范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！