Understanding Excel Range

Topic

New Reply

I was mildly surprised that the code Hans used [post=”773191″]here[/post] worked with the line

Code:

Range(oCell, Range("Z65536")).ClearContents

without the address property of oCell (as shown below), used.

Code:

Range(oCell.Address, Range("Z65536")).ClearContents

But work it did. This led me to do a little testing through which I discovered that the following code works through line 30, but fails at line 40.

Code:

Sub test()
		   Dim oCell As Range
	 10		Set oCell = Cells(4, 1)
	 20		Range(oCell.Address) = Timer
	 30		Range(oCell, oCell) = Timer
	 40		Range(oCell) = Timer
	 End Sub

Can someone help me understand what is going on?

Reply | Quote

Replies

The Range property has two forms:

1) Range(reference) where reference is a string that can contain a cell address, a range address or a defined name.

Examples:

Range(“A1”)
Range(“A1:C10”)
Range(“NorthEast”)

Of course, you can use a string variable or expression instead of a literal reference.

2) Range(cell1, cell2) where cell1 and cell2 are Range objects that refer to a single cell.

Examples:

Range(Range(“A1”), Range(“C10”))
Range(Cells(1, 1), Cells(10, 3))

You can use variables of type Range instead of explicit range objects.

Your example in line #20 uses the first syntax: the single argument oCell.Address is a string. This is correct.
Line #30 uses the second syntax: both arguments are of type Range. This is correct too.
Line # 40 fails because you try to mix the two forms: you use a single argument but it’s a range object instead of a string value.

Reply | Quote

Thank you Hans.

Reply | Quote

By the way, the second syntax is more forgiving than the first one: it allows you to use string arguments:

Range(“A1”, “C10”)

and to mix string and range arguments:

Range(“A1”, Range(“C10”)

And if one or both of the arguments refers to more than one cell, you’ll get the smallest rectangular range that encompasses both arguments:

Range(Range(“A1:C5”), Range(“B3:D7”)) is the same as Range(“A1:D7”).

Reply | Quote

Note that there is a somewhat major difference between the lines of code:
Range(oCell, Range(“Z65536”)).ClearContents

Range(oCell.Address, Range(“Z65536”)).ClearContents

In the first, since the other ranges are implicitly defined as being on the active worksheet, that if oCell is an object on a different worksheet, a runtime error will occur.

In the second line of code, using the address property you only get the cell address, unlinked to a particular sheet and this will work no matter what sheet oCell is on. If oCell is on a different sheet, however, its contents will not be cleared as you are not referencing oCell, but the address of ocell on the activesheet.

Steve

Reply | Quote

Note that there is a somewhat major difference between the lines of code:
Range(oCell, Range(“Z65536”)).ClearContents

Range(oCell.Address, Range(“Z65536”)).ClearContents

In the first, since the other ranges are implicitly defined as being on the active worksheet, that if oCell is an object on a different worksheet, a runtime error will occur.

In the second line of code, using the address property you only get the cell address, unlinked to a particular sheet and this will work no matter what sheet oCell is on. If oCell is on a different sheet, however, its contents will not be cleared as you are not referencing oCell, but the address of ocell on the activesheet.

Thanks Steve; a subtly which I completely missed.

Reply | Quote