Excel VBA - 不按顺序选择多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8888382/
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 - select multiple columns not in sequential order
提问by HL8
I would like to select multiplecolumns.
我想选择多个列。
Eg. I want to select column a, b, d, e, g, h
例如。我想要select column a, b, d, e, g, h
I've tried:
我试过了：
Columns("A, B, D, E, G, H").select
I get errormessage: Type mismatch.
我收到错误消息：Type mismatch。
回答by HRgiger
Range("A:B,D:E,G:H").Selectcan help
Range("A:B,D:E,G:H").Select可以帮助
Edit note: I just saw you have used different column sequence, I have updated my answer
编辑说明：我刚刚看到您使用了不同的列顺序，我已经更新了我的答案
回答by GSerg
Some things of top of my head.
我头顶上的一些事情。
Method 1.
方法一。
Application.Union(Range("a1"), Range("b1"), Range("d1"), Range("e1"), Range("g1"), Range("h1")).EntireColumn.Select
Method 2.
方法二。
Range("a1,b1,d1,e1,g1,h1").EntireColumn.Select
Method 3.
方法三。
Application.Union(Columns("a"), Columns("b"), Columns("d"), Columns("e"), Columns("g"), Columns("h")).Select
回答by MORTON WAKELAND JR
Some of the code looks a bit complex to me. This is very simple code to select only the used rows in two discontiguous columns D and H. It presumes the columns are of unequal length and thus more flexible vs if the columns were of equal length.
有些代码在我看来有点复杂。这是一个非常简单的代码，用于仅选择两个不连续的列 D 和 H 中使用的行。它假定列的长度不等，因此与列的长度相等的情况相比更灵活。
As you most likely surmised 4=column D and 8=column H
正如您最有可能推测的那样 4=D 列和 8=H 列
Dim dlastRow As Long
Dim hlastRow As Long
dlastRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
hlastRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row
Range("D2:D" & dlastRow & ",H2:H" & hlastRow).Select
Hope you find useful - DON'T FORGET THAT COMMA BEFORE THE SECOND COLUMN, AS I DID, OR IT WILL BOMB!!
希望你觉得有用 - 不要忘记第二列之前的逗号，就像我所做的那样，否则它会爆炸！！
回答by gavin
As a recorded macro.
作为录制的宏。
range("A:A, B:B, D:D, E:E, G:G, H:H").select
回答by Jonh
Working on a project I was stuck for some time on this concept - I ended up with a similar answer to Method 1 by @GSerg that worked great. Essentially I defined two formula ranges (using a few variables) and then used the Union concept. My example is from a larger project that I'm working on but hopefully the portion of code below can help some other people who might not know how to use the Union concept in conjunction with defined ranges and variables. I didn't include the entire code because at this point it's fairly long - if anyone wants more insight feel free to let me know.
在一个项目上工作时，我在这个概念上被困了一段时间 - 我最终得到了与@GSerg 的方法 1 类似的答案，效果很好。基本上我定义了两个公式范围（使用一些变量），然后使用联合概念。我的示例来自我正在处理的一个更大的项目，但希望下面的代码部分可以帮助其他一些可能不知道如何将联合概念与定义的范围和变量结合使用的人。我没有包含整个代码，因为此时它相当长 - 如果有人想要更多的见解，请随时告诉我。
First I declared all my variables as Public
首先，我将所有变量声明为 Public
Then I defined/set each variable
然后我定义/设置每个变量
Lastly I set a new variable "SelectRanges" as the Union between the two other FormulaRanges
最后，我设置了一个新变量“SelectRanges”作为另外两个 FormulaRanges 之间的联合
Public r As Long
Public c As Long
Public d As Long
Public FormulaRange3 As Range
Public FormulaRange4 As Range
Public SelectRanges As Range
With Sheet8
c = pvt.DataBodyRange.Columns.Count + 1
d = 3
r = .Cells(.Rows.Count, 1).End(xlUp).Row
Set FormulaRange3 = .Range(.Cells(d, c + 2), .Cells(r - 1, c + 2))
FormulaRange3.NumberFormat = "0"
Set FormulaRange4 = .Range(.Cells(d, c + c + 2), .Cells(r - 1, c + c + 2))
FormulaRange4.NumberFormat = "0"
Set SelectRanges = Union(FormulaRange3, FormulaRange4)
