MS Excel VBA - 选择多个不同的动态范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16028531/
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
MS Excel VBA - Selecting multiple distinct dynamic ranges
提问by baarkerlounger
I need to copy the data in various columns that are not next to each other and that will have a variable number of rows (but that variable will have the same value for each column).
我需要复制不同列中的数据，这些列彼此不相邻，并且行数可变（但该变量的每一列都具有相同的值）。
My code so far is as follows:
到目前为止，我的代码如下：
Set MyXL = CreateObject("Excel.Application")
MyXL.Workbooks.Add
MyXL.Worksheets(1).Name = SheetName
Set shtData = Worksheets("Data")
rngDataRowCount = shtData.Range("A1048576").End(xlUp).Row
Set rngData = shtData.Range("A1:I" & rngDataRowCount)
rngData.Copy
MyXL.Worksheets(SheetName).Activate
MyXL.Worksheets(SheetName).Paste
Range("A1").Select
Which works fine as long as the columns are next to each other but doesn't allow me to add others.
只要列彼此相邻但不允许我添加其他列，它就可以正常工作。
I was hoping I could do something like:
我希望我能做这样的事情：
Set rngData = shtData.Range("A1:I, N1:N, P1:R" & rngDataRowCount)
But this statement fails. Alternatively is there any way I can Set multiple ranges and then append to the clipboard? Any help is, of course, appreciated.
但是这个说法失败了。或者有什么方法可以设置多个范围然后附加到剪贴板？当然，任何帮助都值得赞赏。
EDIT:To clarify what I'm trying to achieve let's say I have the following table:
编辑：为了澄清我想要实现的目标，假设我有下表：
_________________________________________________________
| col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 |
_________________________________________________________
| q | w | e | r | t | y | u | i |
_________________________________________________________
| q | w | e | r | t | y | u | i |
_________________________________________________________
I want to be able to select a range say col1, col2, col5 and col8 so that it copies all filled cells in those columns in one go so that pasting gives me this:
我希望能够选择一个范围，比如 col1、col2、col5 和 col8，以便它一次性复制这些列中所有填充的单元格，以便粘贴给我：
_____________________________
| col1 | col2 | col5 | col8 |
_____________________________
| q | w | t | i |
_____________________________
| q | w | t | i |
_____________________________
回答by Kazimierz Jawor
I think this could be an idea for you, just to use the loop.
我认为这对你来说可能是一个想法，只是为了使用循环。
Edited to make it clear:As a result you will copy each column separately. Column-to-copy order will be kept. If you keep constant destination range rules each column will be copied into appropriate range with the same order.
编辑以明确说明：因此，您将分别复制每一列。将保留按列复制的顺序。如果您保持恒定的目标范围规则，每列将被复制到具有相同顺序的适当范围内。
Sub Answer()
Dim shtData As Worksheet
Set shtData = Worksheets("Data")
Dim rngDataRowCount
rngDataRowCount = shtData.Range("A1048576").End(xlUp).Row
Dim rngData As Range
'<--start of modification
Dim rngCol As Range
Set rngData = shtData.Range("A1:I" & rngDataRowCount & _
",N1:N" & rngDataRowCount & _
",P1:R" & rngDataRowCount)
'<---variant A- keep columns separated into destination range
For Each rngCol In rngData.Columns
rngCol.Copy rngCol.Offset(0, 20) 'here, 20 columns to right
Next
**'here edited**
'<---variant B- copy columns into new location, united, no loop
rngData.Copy Range("zz1") 'here, set destination as single top-left cell
End Sub
One tip- if you copy your columns into other Range use syntax as presented Range.Copy destinationRange
一个提示 - 如果您将列复制到其他范围中，请使用所示的语法 Range.Copy destinationRange
