VBA - 如何在按列号设置范围时设置开始和结束行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16055129/
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
VBA - How to set the start and end row when setting a range by column numbers
提问by baarkerlounger
Suppose I set a range of multiple distinct columns by column number like this:
假设我按列号设置了多个不同列的范围，如下所示：
Set rngData = shtData.Range(Columns(1), Columns(2), Columns(5))
But I only want the range to start at for example row 5 and end at row 10.
但我只希望范围从第 5 行开始到第 10 行结束。
Can I specify this in the Set rngData statement? Appreciate any help
我可以在 Set rngData 语句中指定它吗？感谢任何帮助
EDIT.
编辑。
Thanks to KazJaw this is the solution that worked for me:
感谢 KazJaw，这是对我有用的解决方案：
Dim shtData, rngData
Set shtData = Worksheets("Data")
Set rngData = Union(shtData.Columns(1), shtData.Columns(2), shtData.Columns(5))
Set rngData = Intersect(rngData, shtData.Rows("5:10"))
回答by Kazimierz Jawor
There are few possible ways but I'm not sure which would be best for you. My first and possibly simplest proposal is the following:
有几种可能的方法，但我不确定哪种方法最适合您。我的第一个也可能是最简单的建议如下：
Set rngData = shtData.Range(Columns(1), Columns(2), Columns(5))
set rngData = Intersect(rngData, shtData.Rows("5:10"))
But, your current range will not work anyway. You could use something like this instead:
但是，您当前的范围无论如何都不起作用。你可以使用这样的东西：
Set rngData = shtData.Range("A:B, E:E")
or if you need to keep numeric index of columns than you could go this way (complete code):
或者，如果您需要保留列的数字索引，则可以采用这种方式（完整代码）：
Set rngData = Union(shtData.Columns(1), shtData.Columns(2), shtData.Columns(5))
set rngData = Intersect(rngData, shtData.Rows("5:10"))
Edit- to extend and explain:See below subroutine and comments inside
编辑-扩展和解释：见下面的子程序和里面的注释
Sub Test_Selection()
'Variant A- select range from column 1 to 5
ActiveSheet.Range(Columns(1), Columns(5)).Select
'Variant B- select range of columns: 1, 2 and 5
With ActiveSheet
Union(.Columns(1), .Columns(2), .Columns(5)).Select
End With
'This will not work
ActiveSheet.Range(Columns(1), Columns(2), Columns(5)).Select
End Sub
So, variant B above is able to select not continuous range like this:
因此，上面的变体 B 可以像这样选择不连续的范围：
And final explanation about Intesect, for all who are interested.This procedure would give result as presented in the picture below:
以及关于 Intesect 的最终解释，给所有感兴趣的人。此过程将给出如下图所示的结果：
Sub Final_Combination()
Dim shtData As Worksheet
Set shtData = ActiveSheet
Dim rngData As Range
Set rngData = Union(shtData.Columns(1), shtData.Columns(2), shtData.Columns(5))
Set rngData = Intersect(rngData, shtData.Rows("5:10"))
'or simply combined in one line of code
Set rngData = Intersect(Union(shtData.Columns(1), _
shtData.Columns(2), _
shtData.Columns(5)), _
Rows("5:10"))
rngData.Select
End Sub
