Range() (function)

NEEDS EDITING DOWN

http://excelmatters.com/referring-to-ranges-in-vba/

---

The Range property will accept one or two arguments, with the second one being optional. When using only one argument, it must be an address string (which can include a workbook and/or worksheet name) or the name of a range; whereas if you use two arguments, the arguments can be Range objects, address strings (but these may not include workbook/worksheet names), or the names of ranges, or a combination of these. Hence all of the following are valid:

Range("A1") Range("Sheet2!A1") Range("[Book1]Sheet3!B1") Range("some_named_range") Range("A1", "B10") Range("A1", Range("B10")) Range(Range("A1"), Range("B10")) Range("name1", "name2")

These however are invalid:

Range(Range("A1")) Range("Sheet1!A1", "Sheet1!B10")

Cells

The Cells property will accept one or two arguments, with the second one being optional. When using only one argument, it must be a number which is an index from right to left then top to bottom. When using two arguments, the first is a Row index and the second a Column index. The Row index must be a number, but the column index can be a number or letter.

The following are all valid forms of syntax: Cells(2) – refers to B1 (the second cell from left to right)

Cells(1, 1) – refers to A1 (row 1, column 1)

Cells(1, "C") – refers to C1 (row 1, column C)

Both the Range and Cells properties can be applied to the Application and Worksheet Objects, as well as to Range objects.

When applied to the Application object, the properties generally refer to the currently active sheet but if you pass a range name as the only argument, that range can be on a different worksheet. However you cannot pass two range names as arguments unless they both refer to ranges on the active sheet.

When applied to a Worksheet object, all arguments must refer to ranges on that sheet.

When applied to a Range object, all addressing is relative to that range but the returned Range reference does not have to be part of the original range.

Additionally, when using the Cells property of a Range object, the row and column index numbers can be 0 or negative.

For example: Range("C3").Range("A1") – refers to cell C3 (first row, first column of C3) Range("C3").Range("B1") – refers to cell D3 (first row, second column of C3, even this goes outside the original range) Range("C3").Range("B1:C2") – refers to cells D3:E4 (from first row, second column to second row, third column of C3)

and some examples using Cells: Range("C3").cells(1) – refers to cell C3 (first cell, going left to right) Range("C3").Cells(2) – refers to cell C4 (because the initial range only has one column, cell 2 is the next row) Range("C3:D4").Cells(3) – also refers to cell C4 (going from left to right, top to bottom) Range("C3:D4").Cells(1, 1) – refers to C3 (first row and first column of start range) Range("C3:D4").Cells(0) – refers to cell B3 (this is an oddity!) Range("C3:D4").Cells(0,1) – refers to cell C2 (0th row, so 1 row above the top left cell, and the first column) Range("C3:D4").Cells(-1,0) – refers to cell B1 (two rows above the top left cell, one column to the left)

As you can probably see, this kind of referencing can get a little confusing! It’s usually easier to use the Offset property for this kind of thing.

One final point which often catches people out when passing range objects as arguments to the Range property is that if you need to specify a worksheet object (which is good practice), you need to specify it for all of the Range/Cells properties you use.

So this is wrong:

Sheets("Sheet1").Range(Cells(1, "A"), Cells(10, "B"))

because the two Cells properties do not have a worksheet specified.

Although this may work some of the time, it will be dependent on where the code is (in a worksheet code module, unlike other modules, any use of Range or Cells without a worksheet object reference refers to the sheet containing the code and not the active sheet) and which worksheet is active at the time so sometimes it will fail – this kind of bug can be very hard to track down.

The correct syntax for the above is:

Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, "A"), Sheets("Sheet1").Cells(10, "B"))

or you can use a With … End With block to save a little typing: With Sheets("Sheet1") .Range(.Cells(1, "A"), .Cells(10, "B")) End With

Note the full stops (periods) before the Range and Cells calls.