Excel VBA - 选择动态单元格范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12064731/
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 a dynamic cell range
提问by HL8
I want to be able to dynamically select a range of cells (the heading row), where the row is 1 but the columns with be for 1 to last column, where "A" is the first Column and where "M" is the last column. I know how to find the last column, but I don't know how to modified the below range to input the first and last column as "A" and "M".
我希望能够动态选择一系列单元格（标题行），其中行为 1，但列为 1 到最后一列，其中“A”是第一列，“M”是最后一列柱子。我知道如何找到最后一列，但我不知道如何修改以下范围以将第一列和最后一列输入为“A”和“M”。
Range("A1:M1").Select
回答by Tim Williams
If you want to select a variable range containing all headers cells:
如果要选择包含所有标题单元格的可变范围：
Dim sht as WorkSheet
Set sht = This Workbook.Sheets("Data")
'Range(Cells(1,1),Cells(1,Columns.Count).End(xlToLeft)).Select '<<< NOT ROBUST
sht.Range(sht.Cells(1,1),sht.Cells(1,Columns.Count).End(xlToLeft)).Select
...as long as there's no other content on that row.
...只要该行没有其他内容。
EDIT: updated to stress that when using Range(Cells(...), Cells(...))it's good practice to qualify both Rangeand Cellswith a worksheet reference.
编辑：更新，以强调使用时，Range(Cells(...), Cells(...))它的很好的做法，既合格Range并Cells有工作参考。
回答by akbar moradi
sub selectVar ()
dim x,y as integer
let srange = "A" & x & ":" & "m" & y
range(srange).select
end sub
I think this is the simplest way.
我认为这是最简单的方法。
回答by RocketDonkey
So it depends on how you want to pick the incrementer, but this should work:
所以这取决于你想如何选择增量器，但这应该有效：
Range("A1:" & Cells(1, i).Address).Select
Where iis the variable that represents the column you want to select (1=A, 2=B, etc.). Do you want to do this by column letter instead? We can adjust if so :)
i代表您要选择的列的变量在哪里（1=A、2=B 等）。你想通过列字母来做到这一点吗？如果是这样，我们可以调整:)
If you want the beginning to be dynamic as well, you can try this:
如果你也希望开头是动态的，你可以试试这个：
Sub SelectCols()
Dim Col1 As Integer
Dim Col2 As Integer
Col1 = 2
Col2 = 4
Range(Cells(1, Col1), Cells(1, Col2)).Select
End Sub
回答by aaa
I like to used this method the most, it will auto select the first column to the last column being used. However, if the last cell in the first row or the last cell in the first column are empty, this code will not calculate properly. Check the linkfor other methods to dynamically select cell range.
我最喜欢使用这种方法，它会自动选择第一列到正在使用的最后一列。但是，如果第一行的最后一个单元格或第一列的最后一个单元格为空，则此代码将无法正确计算。检查其他方法的链接以动态选择单元格范围。
Sub DynamicRange()
'Best used when first column has value on last row and first row has a value in the last column
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Set sht = Worksheets("Sheet1")
Set StartCell = Range("A1")
'Find Last Row and Column
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column
'Select Range
sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
End Sub
