Working With Cells

You can fill a range of cells in a specific direction within a worksheet using one of the Fill methods. For example, you may want to fill across a worksheet with the first value in the left corner of the range. VBA offers four Range object methods for filling in a specific direction: FillUp, FillDown, FillRight, and FillLeft.

You can use the FillUp method to fill a range of cells with the value specified in the last cell of the range. For example, if you have the range A1:A10 and apply the FillUp method, as illlustrated, the value in cell A10 copies and pastes in cells A1:A9.

Example:

Range("A1:A10").FillUp

The FillDown method works just opposite of the FillUp method. This method takes the value in the top of the range and copies it to all other cells.

You can use the FillRight method to fill across rows. If you use this method with the range A1:G1, Excel takes the value in cell E1 and pastes it into cells B1 through G1. The FillLeft method works the opposite of the FillRight method. This method takes the value in the last cell on the right, and copies it to all cells in the remaining portion of the range.

□ Type Sheets(WS).Fill AcrossSheets.

0 ype Worksheets("Sheet"). Range("A1:G1"), replacing Worksheets("Sheet"). Range("A1:G1") with the range containing the values to copy.

□ Type Type:=xlFill WithContents, replacing xlFillWithContents with the constant value indicating how to copy values.

Pile Edit

View insert

Format Tools

Data Window

Help

| Type a question for help *

-1» _ ff

*l

iD^SiÜ & s -

-Aria,

- B /

— ■ ■

a "

A23

f"

Denver

A

B

c

D

E

F

G

H

1

!

Sales Totals

2

January

February

March

April

May

June

■1

Atlanta

$55,000.00

$45,000.00

$87,000.00

$65,700.00

$99,000.00

$55

D00.00

4

Boise

$45,000.00

$87,000.00

$55,000.00

$87,000.00

$103,000.00

$45

000.00

5

Dallas Denver

$98,000.00

$55,000.00

$45,000.00

$55,000.00

$45,000.00

$96

1100.00

6

$87,000.00

$55,700.00

$99,000.00

$77,500.00

$65,700.00

$80

D00.00

7

Ft. Worth

$65,700.00

$98,000.00

$41,000.00

$45,000.00

$98,000.00

$103

EO.OO

8

Houston

$99,000.00

$88,000.00

$65,700.00

$99,000.00

$55,000.00

$65

700.00

9

Las Vega Little Roc

$88,000.00

$99,000.00

$98,000.00

$45,000.00

$41,000.00

$87

D00.00

10

$41,000.00

$98,900.00

$53,000.00

$84,500.00

$81,500.00

$98

900.00

11

Los Ange

$103,000.00

$98,000.00

$88,000.00

$41,000.00

$87,000.00

$53

1100.00

12

Miami

$98,900.00

$103,000.00

$55,000.00

$81,500.00

$88,000.00

$55

D00.00

13

Salt Lake

$53,000.00

$65,700.00

$77,500.00

$98,000.00

$84,500.00

$41

EO.OO

14

San Anto

$81,500.00

$41,000.00

$98,900.00

$53,000.00

$77,500.00

$99

1100.00

15

Seattle

$77,500.00

$99,000.00

$81,500.00

$65,700.00

$53,000.00

$90

moo

16

St. Louis

$84,500.00

$88.000.00

$99,000.00

$84,500.00

$98,900.00

$103

110.00

17

18

iy

20

21

W

23

Denver

$87,000.00

$152,700.00

24

-t. Worth

$65,700.00

$163,700.00

'¿b

Houston

$99,000.00

$187,000.00

_

2b

J2Z_

Q Switch to Excel and run the macro.

■ The cell values are copied to each worksheet in the specified range.

PLACE BORDERS AROUND A RANGE OF CELLS

You can use border around cells on your worksheet to make specific information stand out. For example, when a worksheet contains a row of cells that totals the values in the other cells of the worksheet, the total row is typically highlighted in some fashion to make it more noticeable. One common method is to place a border around those cells.

You can add borders to a range of cells using the BorderAround method. When you apply a border to a range of cells, the border outlines the entire range of cells, not each individual cell. When you use this method, it provides different optional parameters that enable you to set the Color, LineStyle, and Weight properties for the Borders collection object associated with the range of cells.

Use the LineStyle parameter to specify the line style for the border around a range. You can specify any one of the XlLineStyle constant values. Excel uses the default value of xlContinuous to draw a continuous line around the range of cells, if you do not specify a LineStyle parameter value.

You can use the Weight parameter to specify the width of the line to border the range of cells. You can specify any one of the XlBorderWeight constant values. If you do not specify a Weight parameter value, Excel uses a default value of xlThin, which draws a thin line around the range of cells.

Use the ColorIndex parameter to specify the border color as an index value to the current color palette specified as a value between 1 and 64 or as one of the XlColorIndex constant values. Specify xlColorIndexAutomatic to use the automatic default line color. You can specify a value of xlColorIndexNone to not use the current color palette.

If you want to specify an RGB color value for the border, use the Color parameter and assign it an RGB color with the RGB function. The RGB color value ensures that you have the same color, regardless of the loaded color palette. With the RGB function, you need to specify three values from 0 to 255 indicating the red, green, and blue component values.

PLACE BORDERS AROUND A RANGE OF CELLS

PLACE BORDERS AROUND A RANGE OF CELLS

'-n Create a new subroutine.

Note: See Chapter 3 for information on creating subroutines.

Type Range("A2:G2").BorderAround, replacing Range("A2:G2") with the cells where the border should be placed.

Type Range("A2:G2").BorderAround, replacing Range("A2:G2") with the cells where the border should be placed.

0 0

Post a comment