Insert A Range

Yo ou can insert a range of cells into a worksheet using the Insert method. When you insert a range of cells into a worksheet, Excel adjusts the values in the existing cells by moving them either down or to the right to allow you to insert into the specified location. For example, if you insert a new row of cells in row 3, Excel shifts the existing values in row 3 down to row 4. Excel shifts all remaining values within the worksheet down. If you add a new column, Excel shifts all existing values right.

It is fairly obvious how the cell values in the worksheet should shift when you add an entire row or column; but, when you insert a smaller block of cells you must instruct Excel how to shift the cells. You use two optional parameters with the Insert method. You use the Shift parameter, and assign it one of the

XLInsertShiftDirection constant values, to make sure the cells shift correctly. Use the xlShiftToRight constant value to have Excel shift the cell values right when you insert a new range of cells. Use the xlShiftDown constant value to shift existing cell values down.

The CopyOrigin parameter accepts only values of True or False and indicates whether to insert a copy of the last range of values you placed in the clipboard. You can add cells to the clipboard either using the Copy and Cut methods or the Copy and Cut options directly in Excel. If the value of the parameter is True, Excel only adds the portion of the copied range that fits the insert range. For example, if you copy cells A5:B10 and then insert cells A1:B2, Excel only inserts the values in cells A5:B6 as the new range values.

INSERT A RANGE

INSERT A RANGE

Ln Create a new subroutine.

Type Range("A3:B4").Copy, replacing Range("A3:B4") with the range of cells to copy.

Ln Create a new subroutine.

Type Range("A3:B4").Copy, replacing Range("A3:B4") with the range of cells to copy.

Note: See Chapter 10 for information on using the Copy method.

0 0

Post a comment