我想创建一个范围表达式以在Union()
方法中使用。
我到目前为止有此代码:
Sub RangeExpresion()
Dim str As String, s
str = "2,10"
s = Split(str, ",")
' (1) If I do this it works. It selects rows 2 and 10
Union(Rows("" & s(0) & ""), Rows("" & s(1) & "")).Select
' (2) If I do this doesn't work. The expression is not correct. I get error
h = "Rows("" & s(0) & ""), Rows("" & s(1) & "")"
Union(h).Select
End Sub
如您所见,在第一个Union()
命令中,我在表达式Rows("" & s(0) & ""), Rows("" & s(1) & "")
中使用并被接受并选择行2和10。
在第二个 Union()
中,我首先尝试将其存储在上面有效的相同表达式中,但是这次我会出现错误
参数不是可选的
如何做到这一点?预先感谢。
联合需要两个范围对象,而不是一个字符串。
您的第一次尝试会生成这些行,因为行不是字符串字面的。您的第二个没有,因此通过一个字符串,而不是两个范围。
联合
返回两个或多个范围的联合。
语法表达。
联合( arg1 , arg2 , arg3 , arg4 , arg5 , arg6 , arg7 , arg8 , arg9 , arg10 , arg11 , arg12 , arg13 , arg14 , arg15 , arg16 , arg17 , arg18 , arg19 , arg20 , arg21 , arg22 em>, arg23 , arg24 , arg25 , arg26 , arg27 arg28 , arg29 , arg30 )
参数
Name Required/Optional Data type Description
Arg1 Required Range At least two Range objects must be specified.
Arg2 Required Range At least two Range objects must be specified.
Arg3 Optional Variant A range.
请求的示例:
Option Explicit
Public Sub RangeExpresion()
Dim str As String, s, rng1 As Range, rng2 As Range
str = "2,10"
s = Split(str, ",")
Set rng1 = rows("" & s(0) & "")
Set rng2 = rows("" & s(1) & "")
Union(rng1, rng2).Select '<==Testing only. You should avoid .Select
End Sub
尽管Jkpieterse对更好方式的评论是真实的。Union(Rows(s(0)), Rows(s(1)))
您必须在S数组上使用循环动态构建统一范围:
Sub RangeExpresion()
Dim str As String
Dim s As String
Dim lItem As Long
Dim rUnifiedRange As Range
str = "2,10"
s = Split(str, ",")
For lItem = LBound(s) To UBound(s)
If rUnifiedRange Is Nothing Then
Set rUnifiedRange = Rows(s(lItem))
Else
Set rUnifiedRange = Union(rUnifiedRange, Rows(s(lItem)))
End If
Next
End Sub