VBA：按变量选择范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12199318/
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: Selecting range by variables
提问by Fabian Stolz
I want to select the formatted range of an Excel sheet. To define the last and first row I use the following functions:
我想选择 Excel 工作表的格式化范围。要定义最后一行和第一行，我使用以下函数：
lastColumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
lastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
In the next step I want to select this area: Formula should look like this:
在下一步中，我想选择此区域：公式应如下所示：
Range(cells(1, 1), cells(lastRow, lastColumn).Select
However, this is not working. Maybe somebody has an idea what is wrong with it. Thanks a lot!
但是，这是行不通的。也许有人知道它有什么问题。非常感谢！
回答by Nikesh Rastogi
I recorded a macro with 'Relative References' and this is what I got :
我用“相对引用”录制了一个宏，这就是我得到的：
Range("F10").Select
ActiveCell.Offset(0, 3).Range("A1:D11").Select
Heres what I thought : If the range selection is in quotes, VBA really wants a STRING and interprets the cells out of it so tried the following:
这就是我的想法：如果范围选择是在引号中，VBA 真的需要一个 STRING 并从中解释单元格，因此尝试以下操作：
Dim MyRange as String
MyRange = "A1:D11"
Range(MyRange).Select
And it worked :) ie.. just create a string using your variables, make sure to dimension it as a STRING variables and Excel will read right off of it ;)
它起作用了:) 即.. 只需使用您的变量创建一个字符串，确保将其标注为 STRING 变量，Excel 将立即读取它；)
Following tested and found working :
经过测试并发现有效：
Sub Macro04()
Dim Copyrange As String
Startrow = 1
Lastrow = 11
Let Copyrange = "A" & Startrow & ":" & "D" & Lastrow
Range(Copyrange).Select
End Sub
回答by Daver
I ran into something similar - I wanted to create a range based on some variables. Using the Worksheet.Cells did not work directly since I think the cell's values were passed to Range.
我遇到了类似的问题 - 我想根据一些变量创建一个范围。使用 Worksheet.Cells 并不能直接工作，因为我认为单元格的值已传递给 Range。
This did work though:
不过这确实有效：
Range(Cells(1, 1).Address(), Cells(lastRow, lastColumn).Address()).Select
That took care of converting the cell's numerical location to what Range expects, which is the A1 format.
这负责将单元格的数字位置转换为 Range 期望的格式，即 A1 格式。
回答by Dick Kusleika
If you just want to select the used range, use
如果您只想选择使用的范围，请使用
ActiveSheet.UsedRange.Select
If you want to select from A1 to the end of the used range, you can use the SpecialCells method like this
如果要从 A1 到使用范围的末尾进行选择，可以像这样使用 SpecialCells 方法
With ActiveSheet
.Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell)).Select
End With
Sometimes Excel gets confused on what is the last cell. It's never a smaller range than the actual used range, but it can be bigger if some cells were deleted. To avoid that, you can use Find and the asterisk wildcard to find the real last cell.
有时 Excel 对最后一个单元格是什么感到困惑。它永远不会比实际使用的范围更小，但如果删除了某些单元格，它可能会更大。为避免这种情况，您可以使用 Find 和星号通配符来查找真正的最后一个单元格。
Dim rLastCell As Range
With Sheet1
Set rLastCell = .Cells.Find("*", .Cells(1, 1), xlValues, xlPart, , xlPrevious)
.Range(.Cells(1, 1), rLastCell).Select
End With
Finally, make sure you're only selecting if you really need to. Most of what you need to do in Excel VBA you can do directly to the Range rather than selecting it first. Instead of
最后，确保您仅在确实需要时才进行选择。您需要在 Excel VBA 中执行的大部分操作都可以直接对 Range 执行，而无需先选择它。代替
.Range(.Cells(1, 1), rLastCell).Select
Selection.Font.Bold = True
You can
你可以
.Range(.Cells(1,1), rLastCells).Font.Bold = True
回答by Daniel
You're missing a close parenthesis, I.E. you aren't closing Range().
您缺少一个右括号，即您没有关闭Range().
Try this Range(cells(1, 1), cells(lastRow, lastColumn)).Select
尝试这个 Range(cells(1, 1), cells(lastRow, lastColumn)).Select
But you should really look at the other answer from Dick Kusleika for possible alternatives that may serve you better. Specifically, ActiveSheet.UsedRange.Selectwhich has the same end result as your code.
但是你真的应该看看 Dick Kusleika 的另一个答案，寻找可能更好地为你服务的替代方案。具体来说，ActiveSheet.UsedRange.Select它与您的代码具有相同的最终结果。
回答by 273K Kool
you are turning them into an address but Cells(#,#) uses integer inputs not address inputs so just use lastRow = ActiveSheet.UsedRange.Rows.countand lastColumn = ActiveSheet.UsedRange.Columns.Count
您将它们转换为地址，但 Cells(#,#) 使用整数输入而不是地址输入，因此只需使用lastRow = ActiveSheet.UsedRange.Rows.count和lastColumn = ActiveSheet.UsedRange.Columns.Count
回答by Chris Bates
I tried using:
我尝试使用：
Range(cells(1, 1), cells(lastRow, lastColumn)).Select
where lastRowand lastColumnare integers, but received run-time error 1004. I'm using an older VB (6.5).
其中lastRow和lastColumn是整数，但收到运行时错误 1004。我使用的是较旧的 VB (6.5)。
What did work was to use the following:
有效的是使用以下内容：
Range(Chr(64 + firstColumn) & firstRow & ":" & Chr(64 + lastColumn) & firstColumn).Select.
