vba 用于在excel表中选择多列的VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45356240/
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 for selecting a number of columns in an excel table
提问by eli-k
As I learned here(also quoted in SO) the following code can be used to select the data-body of column 3 in Table1:
正如我在这里了解到的（也在SO 中引用）以下代码可用于选择第 3 列的数据体Table1：
ActiveSheet.ListObjects("Table1").ListColumns(3).DataBodyRange.Select
I need help to select a number of columns together- say columns 3 to 5, or columns X to X+3 .
我需要帮助一起选择多列- 比如说列 3 到 5 或列 X 到 X+3 。
Using answers to thisquestion I manged to go halfway by using actual column names:
使用这个问题的答案，我设法使用实际的列名进行了一半：
Range("Table1[[Column3]:[Column5]]").Select
But I need to be able to use column numbers instead of names, as they will be the result of a function (i.e. columns X to X+d).
但我需要能够使用列号而不是名称，因为它们将是函数的结果（即列 X 到 X+d）。
回答by
For a contiguous range, simply resize a single column.
对于连续范围，只需调整单个列的大小。
ActiveSheet.ListObjects("Table1").ListColumns(3).DataBodyRange.Resize(, 3).Select
For a more complex selection, use Union to collect them prior to the .Select process.
对于更复杂的选择，请在 .Select 过程之前使用 Union 收集它们。
With ActiveSheet.ListObjects("Table1")
Union(.ListColumns(3).DataBodyRange, _
.ListColumns(4).DataBodyRange, _
.ListColumns(5).DataBodyRange).Select
End With
See How to avoid using Select in Excel VBA macrosfor better methods.
请参阅如何避免在 Excel VBA 宏中使用 Select 以获得更好的方法。
回答by jspek
Use Columns method on DataBodyRange which can take a relative table range such as "A:B"
在 DataBodyRange 上使用 Columns 方法，它可以采用相对表范围，例如 "A:B"
So if you wanted the first two columns you could write: ActiveSheet.ListObjects("Table1").DataBodyRange.Columns("A:B").Select
所以如果你想要前两列，你可以写： ActiveSheet.ListObjects("Table1").DataBodyRange.Columns("A:B").Select
But what if you wanted to select based on a relative column number? Create a few functions to convert numbers to this string:
但是如果您想根据相对列号进行选择呢？创建一些函数来将数字转换为这个字符串：
Sub selectMultipe()
ActiveSheet.ListObjects("Table1").DataBodyRange.Columns(getRangeStr(1, 2)).Select
End Sub
'Get Range String
Function getRangeStr(startColNum As Long, endColNum As Long) As String
startCol = ColLtr(startColNum)
endCol = ColLtr(endColNum)
getRangeStr = startCol + ":" + endCol
End Function
'Convert column number to letter
Function ColLtr(iCol As Long) As String
If iCol > 0 And iCol <= Columns.Count Then ColLtr = Replace(Cells(1, iCol).Address(0, 0), 1, "")
End Function
Note: The column number to letter function was found here
注意：列号到字母函数可以在这里找到
