范围作为变量 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19849907/
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
Range as a Variable VBA
提问by Trung Tran
I am trying to copy values from sheet1 to sheet2. The number of rows of sheet1 varies so I need to store it in a variable. I need something like:
我正在尝试将值从 sheet1 复制到 sheet2。sheet1 的行数各不相同，因此我需要将其存储在变量中。我需要类似的东西：
Worksheets("Sheet1").Range("A2:Ab").Select
, where "b" is a variable that stores the number of rows in Sheet1.
，其中“b”是一个变量，用于存储 Sheet1 中的行数。
Thank you.
谢谢你。
回答by PatricK
You can actually store the UsedRangeof a worksheet. Or just copy to another sheet directly. e.g.
您实际上可以存储UsedRange工作表的 。或者直接复制到另一张纸上。例如
Set oWS1 = Worksheets("Sheet1")
Set oWS2 = Worksheets("Sheet2")
oWS2.UsedRange.Clear ' Clear used range in Sheet2
oWS1.UsedRange.Copy oWS2.Range("A1") ' Copies used range of Sheet1 to A1 of Sheet2
'Set oRng = oWS1.UsedRange ' Sets a Range reference to UsedRange of Sheet1
To get the last row of a sheet:
要获取工作表的最后一行：
lLastRow = oWS1.UsedRange.SpecialCells(xlLastCell).Row
EDIT (Getting Last Row from UsedRange's Address):
编辑（从 UsedRange 的地址获取最后一行）：
Dim sAddr as String, lLastRow As Long
sAddr = oWS1.UsedRange.Address
lLastRow = CLng(Mid(sAddr, InStrRev(sAddr , "$") + 1))
回答by shree.pat18
One way would be to dynamically create the cell name by concatenating your column and row identifiers and then using the Range(Cell1,Cell2) overload, as below:
一种方法是通过连接列和行标识符，然后使用 Range(Cell1,Cell2) 重载来动态创建单元格名称，如下所示：
Dim rng As Range
Dim intStart As Integer
Dim intEnd As Integer
Dim strStart As String
Dim strEnd As String
'Let's say your range is A2 to A10
intStart = 2
intEnd = 10
strStart = "A" & intStart
strEnd = "A" & intEnd
Set x = Range(strStart, strEnd)
