The Range property

The Range property returns a Range object. If you consult the Help system for the Range property, you learn that this property has two syntaxes:

object.Range(cell1) object.Range(cell1, cell2)

Working with Merged Cells

Working with merged cells can be tricky. If a range contains merged cells, you may need to take some special action with the macros. For example, if cells A1:D1 are merged, the statement that follows selects columns A through D (not just column B, as you might expect):

Columns("B:B").Select

I don't know if this unexpected behavior is intentional or if it's a bug. However, it can cause your macro to behave in a manner that you didn't expect. Merged cells also cause problems with sorting.

To determine if a particular range contains any merged cells, you can use the following VBA function. The function returns True if any cell in the argument range is a merged cell (refer to Chapter 10 for more information about Function procedures).

Function ContainsMergedCells(rng As Range) Dim cell As Range ContainsMergedCells = False For Each cell In rng

If cell.MergeCells Then

ContainsMergedCells = True Exit Function End If Next cell End Function

To refer to merged cells, you can reference the entire merged range or just the upperleft cell within the merged range. For example, if a worksheet contains four cells merged into one (A1, B1, A2, and B1), reference the merged cells using either of the following expressions:

If you attempt to assign a value to a cell in a merged range that's not the upper-left cell, VBA ignores the instruction and does not generate an error. For example, the following statement has no effect if A1:B2 is merged:

Some operations cause Excel to display a confirmation message. For example, if A1:B2 is merged, the following statement generates a message: This operation will cause some merged cells to unmerge. Do you wish to continue?

Range("B2").Delete

Bottom line? Be careful with merged cells. Clearly, this feature was not very well thought out before it was implemented._

The Range property applies to two types of objects: a Worksheet object or a Range object. Here, celll and cell2 refer to placeholders for terms that Excel recognizes as identifying the range (in the first instance) and delineating the range (in the second instance). Following are a few examples of using the Range property.

You've already seen examples like the following one earlier in the chapter. The instruction that follows simply enters a value into the specified cell. In this case, it puts the value l2.3 into cell A1 on Sheetl of the active workbook:

Worksheets("Sheetl").Range("Al").Value = l2.3

The Range property also recognizes defined names in workbooks. Therefore, if a cell is named Input, you can use the following statement to enter a value into that named cell:

Worksheets("Sheetl").Range("Input").Value = l00

The example that follows enters the same value into a range of 20 cells on the active sheet. If the active sheet is not a worksheet, this causes an error message:

ActiveSheet.Range("Al:Bl0").Value = 2

The next example produces exactly the same result as the preceding example:

The sheet reference is omitted, however, so the active sheet is assumed. Also, the value property is omitted, so the default property (which is Value for a Range object) is assumed. This example also uses the second syntax of the Range property. With this syntax, the first argument is the cell at the top left of the range, and the second argument is the cell at the lower right of the range.

The following example uses the Excel range intersection operator (a space) to return the intersection of two ranges. In this case, the intersection is a single cell, C6. Therefore, this statement enters 3 into cell C6:

And finally, this next example enters the value 4 into five cells: that is, a noncontiguous range. The comma serves as the union operator:

So far, all the examples have used the Range property on a Worksheet object. As I mentioned, you can also use the Range property on a Range object. This can be rather confusing, but bear with me.

Following is an example of using the Range property on a Range object. (In this case, the Range object is the active cell.) This example treats the Range object as if it were the upper-left cell in the worksheet, and then it enters a value of 5 into the cell that would be B2. In other words, the reference returned is relative to the upper-left corner of the Range object. Therefore, the statement that follows enters a value of 5 into the cell directly to the right and one row below the active cell:

ActiveCell.Range("B2") = 5

I said this is confusing. Fortunately, there is a much clearer way to access a cell relative to a range: the offset property. I discuss this property after the next section.

0 0

Responses

  • William Brown
    How to put merged cell into macro range select command?
    8 years ago

Post a comment