Defining Ranges

Besides referencing specific blocks of cells to create Range objects, you can specify a Row or Column as a Range object with the Rows and Columns properties. If you specify an entire column as a range selection, Excel creates a Range object containing the specified column. For example, the following code selects Column B and the selected column:

SelectedColumn = Columns(2).

You can use the Rows property to specify a row you want to use as a Range object. With this property, Excel selects the entire row as the Range object: SelectedRow = Rows(3).

You can also use each of these properties separately to refer to the entire group of columns or the entire group of rows within the worksheet. For example, the following code refers to the current columns:

Set ColRange = Columns.

You can use the Rows and Columns properties to determine a specific column or row within a range. For example, if you have a range of cells from B5 to G10, using the Rows parameter you can select the first row within the specified range. You can accomplish this by specifying the desired row, as shown in this code.

Set RngObj = Range("B5:G10") RngObj.Rows(1).Select

L0 Type Set NewRange = FirstRange.Offset(3, 3), replacing 3,3 with the offset for the new range.

Q Type additional VBA code for processing the new range.

■ Excel highlights the newly created range with the Offset property on the worksheet.

L0 Type Set NewRange = FirstRange.Offset(3, 3), replacing 3,3 with the offset for the new range.

Q Type additional VBA code for processing the new range.

■ Excel highlights the newly created range with the Offset property on the worksheet.

DELETE A RANGE OF CELLS

You can remove a specific range of cells from a worksheet using the Delete method. When you delete a range of cells, Excel completely removes the specified cells and adjusts the remaining values within the worksheet to fill in the gap left by the deletion. For example, if you remove an entire column of values, such as column B, Excel shifts the values in column C left and they become the new column B values. All remaining column values shift left. Conversly, if you delete a row, Excel shifts all values in the rows below up one row.

Excel easily determines how to shift the cells when you remove entire rows and columns, but if you just remove a block of cells, you must specify how the remaining values fill to ensure you get the anticipated results. You can specify how Excel shifts the cells using the Shift parameter with the Delete method. When you use the Shift parameter, you assign it one of the XLDeleteShiftDirection constant values. The first value, xlShiftToLeft, tells Excel how to shift values to the left to fill the gap created by the deletion. The xlShiftUp constant value indicates that Excel should shift the values up from below the deletion to fill the gap.

Keep in mind that Excel ignores the Shift parameter value if it is not a valid shift direction for the deleted range. For example, the code Column(2).Delete Shift:=xlShiftUp deletes a specific column, but Excel still shifts the cells left; because you removed the entire column there are no cells to shift up. Although these instances may occur occasionally, for best results, remember to specify how to shift the cells, so that when Excel has a choice, your cells shift in the appropriate direction.

DELETE A RANGE OF CELLS

DELETE A RANGE OF CELLS

'-n Create a new subroutine.

B Type Dim RangeDelete Type Set RangeDelete =

As Range, replacing Range("A1:B4"), replacing

RangeDelete with the Range("A1:B4") with name of the range to delete. the range to delete.

'-n Create a new subroutine.

B Type Dim RangeDelete Type Set RangeDelete =

As Range, replacing Range("A1:B4"), replacing

RangeDelete with the Range("A1:B4") with name of the range to delete. the range to delete.

0 0

Post a comment