How to create a range from two ranges in VBA?
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.
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
Method 4 is not the same as Method 1 when the ranges are not adjacent.
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)
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
So Range("A2")
can be written as Cells(2,1)