VBA - 使用数字选择列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26111825/
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 - Select columns using numbers?
提问by gemmo
I'm looking for an alternative to this code, but using numbers.
I want to select 5 columns, the start column is a variable, and then it selects 5 columns from this.
我正在寻找替代方案code，但使用数字。我想选择5列，起始列是一个变量，然后从中选择5列。
Columns("A:E").Select
How do I use integersinstead, to reference columns? Something like below?
我该如何使用integers来引用列？像下面这样的？
For n = 1 to 5
Columns("n : n + 4") .select
do sth
next n
Thanks. Gemmo
谢谢。杰莫
回答by L42
You can use resize like this:
您可以像这样使用调整大小：
For n = 1 To 5
Columns(n).Resize(, 5).Select
'~~> rest of your code
Next
In any Range Manipulationthat you do, always keep at the back of your mind Resize and Offsetproperty.
在您执行的任何范围操作中，始终将Resize 和 Offset属性牢记在心。
回答by McPaddy
Columns("A:E").Select
Can be directly replaced by
可以直接替换
Columns(1).Resize(, 5).EntireColumn.Select
Where 1 can be replaced by a variable
其中 1 可以替换为变量
n = 5
Columns(n).Resize(, n+4).EntireColumn.Select
In my opinion you are best dealing with a block of columns rather than looping through columns n to n + 4 as it is more efficient.
在我看来，您最好处理一个列块，而不是循环从 n 列到 n + 4 列，因为这样效率更高。
In addition, using select will slow your code down. So instead of selecting your columns and then performing an action on the selection try instead to perform the action directly. Below is an example to change the colour of columns A-E to yellow.
此外，使用 select 会减慢您的代码速度。因此，不要选择您的列然后对选择执行操作，而是尝试直接执行操作。下面是将列 AE 的颜色更改为黄色的示例。
Columns(1).Resize(, 5).EntireColumn.Interior.Color = 65535
回答by SeanC
you can use rangewith cellsto get the effect you want (but it would be better not to use select if you don't have to)
您可以使用rangewithcells来获得您想要的效果（但如果您不需要，最好不要使用 select）
For n = 1 to 5
range(cells(1,n).entirecolumn,cells(1,n+4).entirecolumn).Select
do sth
next n
回答by Nam Taf
Try using the following, where nis your variable and x is your offset (4 in this case):
尝试使用以下内容，其中n是您的变量，x 是您的偏移量（在本例中为 4）：
LEFT(ADDRESS(1,n+x,4),1)
This will return the letter of that column (so for n=1 and x=4, it'll return A+4 = E). You can then use INDIRECT()to reference this, as so:
这将返回该列的字母（因此对于 n=1 和 x=4，它将返回 A+4 = E）。然后您可以使用它INDIRECT()来引用它，如下所示：
COLUMNS(INDIRECT(LEFT(ADDRESS(1,n,4),1)&":"&LEFT(ADDRESS(1,n+x,4),1)))
which with n=1, x=4 becomes:
其中 n=1, x=4 变为：
COLUMNS(INDIRECT("A"&":"&"E"))
and so:
所以：
COLUMNS(A:E)
回答by Burak Borhan
no need for loops or such.. try this..
不需要循环等..试试这个..
dim startColumnas integer
dim endColumn as integer
startColumn = 7
endColumn = 24
Range(Cells(, startColumn), Cells(, endColumn)).ColumnWidth = 3.8 ' <~~ whatever width you want to set..*
回答by Pieter Geerkens
You can specify addresses as "R1C2" instead of "B2". Under File -> Options -> Formuals -> Workingg with Formulasthere is a toggle R1C1 reference style. which can be set, as illustrated below.
您可以将地址指定为“R1C2”而不是“B2”。在File -> Options -> Formuals -> Workingg with Formulas 下有一个切换R1C1 参考样式。可以设置，如下图。
回答by dapaz
I was looking for a similar thing. My problem was to find the last column based on row 5 and then select 3 columns before including the last column.
我正在寻找类似的东西。我的问题是根据第 5 行找到最后一列，然后在包含最后一列之前选择 3 列。
Dim lColumn As Long
lColumn = ActiveSheet.Cells(5,Columns.Count).End(xlToLeft).Column
MsgBox ("The last used column is: " & lColumn)
Range(Columns(lColumn - 3), Columns(lColumn)).Select
Message box is optional as it is more of a control check. If you want to select the columns after the last column then you simply reverse the range selection
消息框是可选的，因为它更像是一个控制检查。如果要选择最后一列之后的列，则只需反转范围选择
Dim lColumn As Long
lColumn = ActiveSheet.Cells(5,Columns.Count).End(xlToLeft).Column
MsgBox ("The last used column is: " & lColumn)
Range(Columns(lColumn), Columns(lColumn + 3)).Select
回答by Edy_Tang
In this way, you can start to select data even behind column "Z" and select a lot of columns.
这样，您甚至可以开始选择“Z”列后面的数据并选择很多列。
Sub SelectColumNums()
Dim xCol1 As Integer, xNumOfCols as integer
xCol1 = 26
xNumOfCols = 17
Range(Columns(xCol1), Columns(xCol1 + xNumOfCols)).Select
End Sub
回答by Dave F
In the example code below I use variables just to show how the command could be used for other situations.
在下面的示例代码中，我使用变量只是为了展示如何将命令用于其他情况。
FirstCol = 1
LastCol = FirstCol + 5
Range(Columns(FirstCol), Columns(LastCol)).Select
