Menu
I have two ranges, each containing a single cell (for example "A1" and "C3").
How do I get a new range containing all the cells between these two ("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").
663
One quick way to do this is by holding the Ctrl key and then selecting the cells or ranges. Excel will automatically add the commas between the range references in the formula. This is great for functions like SUM, COUNTIFS, SUMIFS, VLOOKUP, or any function that has arguments for multiple arrays (ranges).
In Excel, the general method to combine ranges is to apply Copy and Paste features. Select the first range and hold Ctrl key then press C to copy it, then go to a new worksheet and place the cursor at a cell, and press Ctrl + V to paste the first range.
The basic syntax of the VBA range property consists of the keyword “Range” followed by the parentheses. The relevant range is included within double quotation marks. For example, the following reference refers to cell C1 in the given worksheet and workbook.
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)
200
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
