Excel VBA 选择多个动态范围
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/21616455/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA selecting multiple dynamic ranges
提问by Hinson.Li
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
回答by Netloh
The problem occurs because of the comma in you range statements. I.e. when you set R1
you should write:
出现问题的原因是范围语句中的逗号。即当你设置R1
你应该写:
Set R1 = Range("A7:A" & LR)
Also, when you define the object type of your variables R1
, ..., R5
you should write it as
此外,当您定义变量的对象类型时R1
,...,R5
您应该将其写为
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.
否则R1
,...,R5
将被定义为变体。这不会导致问题,但会节省内存并使代码更清晰。
回答by L42
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:
你所做的是你有点混淆了 Range 语法。
请参阅下面的一些常见范围语法:
Using :
to define Range
:
使用:
定义Range
:
Range("A1:A" & LR) '~~> where LR holds the last row number
Using ,
to define Range
:
使用,
定义Range
:
Range("A1","A" & LR)
Using Cells
property:
使用Cells
属性:
Range(Cells(1, "A"),Cells(LR, "A"))
Range(Cells(1, 1),Cells(LR, 1)) '~~> another way
Using Range
property:
使用Range
属性:
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.
以上所有语法评估为:$A$1:$A$(LR)
每个都有一定的优势和用途。
使用您最熟悉的语法。
Additional:
额外的:
This one uses Intersect Function
:
这个使用Intersect Function
:
Set R1 = Intersect(Columns("A:A"),Rows("1:" & LR))