vba 如何使用excel中的单元格值在vba中指定范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28341642/
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
how to specify a range in vba using a cell value in excel
提问by siddharth_001
I am very new to vba and basically only use it when amending a range value (having previously recorded the script using a macro in excel). Since I add/delete rows in excel I have to go to the vba script and manually update the range part. So if my range on the excel sheet has expanded from A2:R83 to A2.R84 heres one of the parts I update from:
我对 vba 非常陌生，基本上只在修改范围值时使用它（之前使用 excel 中的宏记录了脚本）。由于我在 excel 中添加/删除行，因此我必须转到 vba 脚本并手动更新范围部分。因此，如果我在 Excel 表上的范围已从 A2:R83 扩展到 A2.R84，那么我更新的部分之一是：
Range("A2:R83").Select
Range("A2:R83").Select
To:
到：
Range("A2:R84").Select
Range("A2:R84").Select
Is there a way I can specify a cell that vba can take the range values from? eg can I, on the excel sheet cell X1input A2and in cell Y2input R84and have the vba script reference these cells to determine the current range?
有没有办法可以指定 vba 可以从中获取范围值的单元格？例如，我可以在excel表格单元格X1输入A2并在单元格Y2输入R84并让vba脚本引用这些单元格来确定当前范围吗？
Appreciate any help!
感谢任何帮助！
回答by eirikdaude
I believe this will do what you want, :
我相信这会做你想做的，：
Sub test()
Dim s1 As String, s2 As String
s1 = Sheet1.Range("A1"): s2 = Sheet1.Range("B1")
Range(s1 & ":" & s2).Select
End Sub
You will, however, run into trouble if the values in A1 and B1 are not valid cell-names, so some input validation may be a good idea.
但是，如果 A1 和 B1 中的值不是有效的单元格名称，您将遇到麻烦，因此一些输入验证可能是一个好主意。
回答by Excel Sigma
I found that it is possible to make the validation range dinamic using INDIRECT.
我发现可以使用 INDIRECT 使验证范围动态化。
1.- In location that you choose (in the example I use X1 in sheet1) put
1.- 在您选择的位置（在示例中我在 sheet1 中使用 X1）放置
="'Sheet1'!"&"A2:"&"R"&COUNTA(R1:R2000)
I put R2000 to have plenty space in case the range grows, change to a size that suits you. The result of this formula will be a range. Its size will change every time you put something new in R because of the Counta.
我把 R2000 放在有足够的空间，以防范围扩大，换一个适合你的尺寸。这个公式的结果将是一个范围。由于Counta.
2.- In the validation section place this formula when you record it.
2.- 在验证部分，在记录时放置此公式。
=INDIRECT('Sheet'!$X)
This makes the validation read the range based on what x1 says.
这使得验证读取基于 x1 所说的范围。
回答by Davesexcel
This will figure your range,
这将计算您的范围，
Sub SelectRng()
Dim Rws As Long, Rng As Range
Rws = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(2, "A"), Cells(Rws, "R"))
Rng.Select 'or whatever you want to do with it.
End Sub
