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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-08 17:44:28  来源:igfitidea点击:

Excel VBA selecting multiple dynamic ranges

excelvbadynamicrangeunion

提问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 R1you should write:

出现问题的原因是范围语句中的逗号。即当你设置R1你应该写:

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

Also, when you define the object type of your variables R1, ..., R5you 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, ..., R5will 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 Cellsproperty:

使用Cells属性:

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

Using Rangeproperty:

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