Defining Ranges

Exl Lra I You can use the Select method with a

Range object to highlight a cell or range of cells on a worksheet. For example, to select the range of cells from A3 to B6, you type Range("A3:A6").Select.

When you use the Select method with a Range object, the active cell becomes the first cell in the specified range. If you specify individual cells with the Select method, the active cell is the first cell specified. For example, Range( "A3, A1, A5").Select selects cell A3 as the active cell.

You can also use the Activate method to highlight a cell or range of cells. With the Activate method, the first cell referenced in the range is the active cell, but all of the other cells in the range are highlighted to indicate that they are also selected. For example, in the code Range( "B4:C6" ). Activate, Excel marks B4 as the active cell and highlights the remaining cells. Keep in mind, when you use the Select method, the first cell in the range is also marked as the active cell. This makes the two methods totally interchangeable when dealing with ranges.

USING THE RANGE PROPERTY WITH NAMES_

0 Type Range("A3: C7, E1:F3").Select, replacing A3:C7 with the first range of cells and E1:F3 with a second range of cells.

USING THE RANGE PROPERTY WITH NAMES_

-D Create a new subroutine

0 Type Range("A3: C7, E1:F3").Select, replacing A3:C7 with the first range of cells and E1:F3 with a second range of cells.

-m

File

Edit View Insert Format Tools

Data Window Help

¡Type a q

„«t„„f„,

•IP - .

0 X

i □

&

a & m #

a jt ^

« • 1 % 2 - a

U® -i Ar

d e

B / U

A3

-

f* Jones, John

G

—i

i

Employees

Title

Salary

Years of Service

H

2

Smitl

, Sam

Sr. Developer

$90,000.00

3

■¿

Jones

. John

Accountant

$95,000.00

4

Smith, Fred

Vice President

$145,000.00

9

5

Hans

n, April

Receptionist

$25,000.00 1

H

Ande

son Tom

Sr. Developer

$93,000.00

3

7

$85,000.00

2

8

Peterson, Paul

Systems

$82,000.00

2

9

Garc

a, Juan

Software Develope

$81,000.00

3

10

Andre

ws. Andy

Sales Manager

$100,000.00

6

11

Wilson, Sam

Northeast Sales

$75,000.00

5

12

Jones, Wendy

Secretary

$25,000.00

2

13

Smith, Fred

CTO

$195,000.00

12

14

Jense

n, George

CEO

$195,000.00

12

15

Adarr

s, Mary

Systems

$85,000.00

4

1b

Olser

, John

Human Resources

$55,000.00

3

17

Ande

son, Richard

Janitor

$30,000.00

3

18

ParkE

r, Sid

Maintenance

$30,000.00

2

1H

1 lavis

Don

Security

$45,000.00

4

20

Johns

on, Jerry

Sr. Developer

$87,000.00

6

21

Harris

on, Rick

Security

$45,000.00

2

71

23

24

yh

26

2/

I ^

14

► »1

, Sheet 1 / Shee

2 / 5heet3 /

I

y

Su m=443000

-■ The specified range of cells is selected.

USING THE CELLS PROPERTY

ou can use the Cells property to reference specific cells in a worksheet, allowing you to make changes to the values or properties of the cells, such as the font settings. The Excel Object Model does not contain a Cells object, so in order to reference specific cells you use either the Cells property or the Range property, each of which actually returns a Range object with the specified cells. See the section "Using the Range Property" for more information on the Range property. One big difference in the two properties is that the Cells property, when you use it with its two parameters, returns only a single cell, whereas you typically use the Range property to return a series of cells.

You can use the Cells property with the Application, Range, and Worksheet objects. When you use it with the Application and Worksheet objects, you return the same result. For example, you can type Cells, Application.Cells, or ActiveSheet.Cells to return a Range object containing all cells in the active worksheet.

If you use the Cells property with the optional parameters, you can reference a specific cell within the worksheet. The first parameter, row, contains an integer value between 1 and 65536 indicating the row index. The second parameter, column, contains an integer value between 1 and 256 indicating the column index. For example, using this method to reference cell B5, you assign a value of 5 for the row parameter and a value of 2 for the column parameter, as shown in this code: Cells(5,2).

One big advantage of using the Cells property instead of the Range property is that you can utilize variables to easily change the integer values. For example, you can use a variable to represent either the row or column, as shown in this code: Cells(N,1) = 5, which sets the value of the cell in column A and the row specified by N to 5.

USING THE CELLS PROPERTY

USING THE CELLS PROPERTY

'-n Create a new subroutine.

LH Type For N = 1 To 10, replacing 10 with the number of cells to modify.

< Type Cells(N, 1) = N, replacing N with the value to assign to each cell.

'-n Create a new subroutine.

LH Type For N = 1 To 10, replacing 10 with the number of cells to modify.

< Type Cells(N, 1) = N, replacing N with the value to assign to each cell.

0 0

Post a comment