我有两个范围,每个范围都包含一个单元格(例如"A1"和"C3")。
如何获取一个新范围,其中包含这两个范围之间的所有单元格("A1:C3")?
我尝试了以下代码:
Set NewRange = Range(Range1.Address:Range2.Address)
我该如何以 R1C1 格式设置范围?我想使用类似于 Range("R1C2") 而不是 Range("A2") 的东西。
像这样?
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
不要使用R1C1格式,改用Cells(r,c)
。这将给您更多的灵活性和控制。
因此,Range("A2")
可以写成Cells(2,1)
。
您可以以多种方式设置新的范围。以下是几个示例。要获取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
$A$1:$C$3
,它会给你 $A$1,$C$3
。 - Siddharth Rout当范围不相邻时,方法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)
UNION
是正确的答案! - DrMarbuserng1.parent.Range(rng1m rng2)
或者像@user3484615定义的那样使用With
语句。 - DrMarbuse还有一种可能是:
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.
Private Function CombineRanges(rng1 As Range, rng2 As Range) As Range
Set CombineRanges = ActiveSheet.Range(rng1.Address & ":" & rng2.Address)
End Function
使用方法如下:
Dim NewRange As Range
Set NewRange = CombineRanges(Range1, Range2)
Union()
具有相同的目的,但仅适用于2个范围。 - Marcucciboy2