如何从 VBA 中的两个范围创建一个范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12527725/
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 create a range from two ranges in VBA?
提问by grozhd
I have two ranges, each containing a single cell (for example "A1" and "C3").
我有两个范围，每个范围都包含一个单元格（例如“A1”和“C3”）。
How do I get a new range containing all the cells between these two ("A1:C3")?
如何获得包含这两个单元格之间的所有单元格的新范围（“A1:C3”）？
I tried this:
我试过这个：
Set NewRange = Range(Range1.Address:Range2.Address)
Also how do I set a range in R1C1 format? I want to use something like Range("R1C2") instead of Range("A2").
另外我如何设置 R1C1 格式的范围？我想使用 Range("R1C2") 而不是 Range("A2") 之类的东西。
回答by Siddharth Rout
Like this?
像这样？
Sub Sample()
Dim rng1 As Range, rng2 As Range
Dim NewRng As Range
With ThisWorkbook.Sheets("Sheet1")
Set rng1 = .Range("A1")
Set rng2 = .Range("C3")
Set NewRng = .Range(rng1.Address & ":" & rng2.Address)
Debug.Print NewRng.Address
End With
End Sub
Instead of R1C1 format use Cells(r,c). That will give you more flexibility + control
而不是 R1C1 格式使用Cells(r,c). 这会给你更多的灵活性+控制
So Range("A2")can be written as Cells(2,1)
所以Range("A2")可以写成Cells(2,1)
回答by user3357963
You can set the a new range in various ways. Below are a few examples. To get R1C1 format - I personally find it easier entering the normal formula and then using VBA to extract the R1C1 format required. See the debug.print statements below.
您可以通过多种方式设置新范围。下面是几个例子。要获得 R1C1 格式 - 我个人觉得输入普通公式然后使用 VBA 提取所需的 R1C1 格式更容易。请参阅下面的 debug.print 语句。
Sub test()
Dim rng1 As Range
Dim rng2 As Range
Dim newRng As Range
With Sheet1
Set rng1 = .Range("A1")
Set rng2 = .Range("C3")
Debug.Print rng1.FormulaR1C1
Debug.Print rng1.FormulaR1C1Local
'Method1
Set newRng = .Range(rng1, rng2)
'method2
Set newRng = .Range(rng1.Address, rng2.Address)
'method3 (Only works if rng1 & rng2 are single cells
Set newRng = .Range(rng1.Address & ":" & rng2.Address)
'method4
Set newRng = Union(rng1, rng2)
End With
End Sub
回答by user3484615
Method 4 is not the same as Method 1 when the ranges are not adjacent.
当范围不相邻时，方法 4 与方法 1 不同。
With Sheet1
Set rng1 = .Range("A1:A3")
Set rng2 = .Range("C1:C3")
'This combines the two separate ranges, so select A1, A2, A3, C1, C2, C3
set newRng = Union(rng1, rng2)
'This combines the two ranges in the same way as when using "A1:C3",
'so including the cells from column B
set newRng = .Range(rng1, rng2)
回答by Roberto Santos
It′s also possible something like:
也有可能是这样的：
Dim newRange as Range
Set newRange = Range("A1:A4,A7:A9,C1:D9") 'Three different regions grouped
'or
Set newRange = Range("A1:A4,A7:A9,C1:D9, D10:D11") 'Four different regions grouped.
'or
Set newRange = Range("A1:A4,A7:A9,C1:D9, D10:D11, ...") 'N different regions grouped.
回答by thanos.a
Put this in a module:
把它放在一个模块中：
Private Function CombineRanges(rng1 As Range, rng2 As Range) As Range
Set CombineRanges = ActiveSheet.Range(rng1.Address & ":" & rng2.Address)
End Function
Use it like:
像这样使用它：
Dim NewRange As Range
Set NewRange = CombineRanges(Range1, Range2)
