Defining Ranges

You can use one of the different Clear methods to clear the contents of a cell or range of cells within your worksheet. The Clear method clears the entire contents, including cell values, formatting, and formulas, from the specified cells. You specify this method using the following syntax:

Example:

RangeVar.Clear

Whatever cells the RangeVar references are cleared of all contents. You can use the ClearFormats method to clear all formatting from the specified range. All cell values and formulas remain in the cells. When you use this method, the contents of the specified range display using default formatting options. You indicate this method using the following syntax:

Example:

RangeVar.ClearFormats

Finally, you can clear the cell values and formulas from a range of cells using the ClearContents method. This method clears everything with the exception of the formatting that you applied to the cells. After using this method, you can add new values to any cells in the range and Excel applies the original formatting. The syntax for this method is similar to the other ones:

Example:

RangeVar.ClearContents

□ Type Set NewRange = Intersect(Range1, Range2).

• Switch to Excel and run the macro.

□ Type Set NewRange = Intersect(Range1, Range2).

Q Type additional VBA code to work with the new combined range of cells.

• Switch to Excel and run the macro.

-■ The macro finds the cells that intersect both ranges.

lift] File Edit View Insert Format lools Data Window

Help

| Type a question for help - _ i3

I □

éBilá âû!?

& 1

& s - 2

m m

* I Arial

s 10

- B i"

U —

--?

B5

r*

A

B

c

D

E

F

G

H

J—

1

Hansen, April

Hansen

April

Sr. Developer $90

000.00

3

2

Anderson, Tom

Anderson

Tom

Accountant

$95

H0.00

4

3

Adams, Jerry

Adams

Jerry

Vice President

£145

D00.00

9

4

Pelerson, Paul

Pfilersnn

ïeceptionist

$25

110.00

1

!

Garcia, Juan

Sr. Developer

$93

H0.00

3

Andrews, Andy

software Develop

$85

D00.00

2

7

Wilson, Sam

Systems

$82

110.00

2

8

Jones, Wendy

Software Develop

$81

H0.00

3

9

Smith, Fred

Sales Manager

$100

1100.00

6

10

Jensen, George

"lortheast Sales

$75

110.00

5

11

Adams, Mary

Secretary

$25

H0.00

2

12

Hansen, April

;to

$195

000.00

12

Anderson, Tom

"EO

$195

D00.00

12

14

Adams, Jerry

Adams

systems

$85

H0.00

4

15

Peterson, Paul

Peterson

Paul

Human Resource

$55

000.00

3

16

Garcia, Juan

Garcia

Juan

Janitor

$30

D00.00

3

17

Andrews, Andy

Andrews

Andy

Maintenance

$3Q

H0.00

2

18

Wilson, Sam

Wilson

Sam

Security

$45

1100.00

4

19

Jones, Wendy

Jones

Wendy

Sr. Developer

$87

D00.00

6

20

Smith, Fred

Smith

Fred

Security

$45

D00.00

2

21

Jensen, George

Jensen

George

Receptionist

$25

1100.00

1

22

Adams, Mary

Adams

Mary

Sr. Developer

$93

D00.00

3

23

24

25

26

27

'1

il

► n|\sheetl / Sheet2 /sheeM }.Sheet5

/ Sheet3 /

►II

y

AND PASTE RANGES OF CELLS

Some of the most commonly used commands with any Microsoft Windows application are the Cut, Copy, and Paste commands. In fact, people use these commands so frequently that most applications have toolbar buttons for accessing them. You can also cut and paste values within a worksheet directly using VBA and the Cut method associated with the Range object.

The Cut method provides the ability to cut the values from a specific range and paste them in either the Windows Clipboard or a specific destination. The Cut method has one optional parameter, Destination, that you can use if you want to specify where you want to paste the cut values. If you do not specify a Destination parameter value, the cut range of values paste into the Windows Clipboard. If you use the Destination parameter, you must specify another Range object as the location for the values to paste. The following code illustrates the use of the Cut method to paste the range in cells A1:A5: CutRange.Cut Destination:=Range( "A1:A5 ").

Keep in mind that you must make the range specified for CutRange variable and the destination range the same size, or Excel returns an error. If you do not know the size of the cut range of cells, you should specify a single cell as the destination range, for example: CutRange.Cut Destination:=Range( "A1")

If you specify only one cell, Excel makes it the initial cell and pastes the values in cells starting at that location. In other words, if pasting the contents requires cells A1:B4, Excel automatically uses those cells.

Keep in mind, however, that when you use the Cut method, the contents are removed from the specified cells and pasted into the new cells. The original cells appear as empty on the worksheet.

CUT AND PASTE RANGES OF CELLS

CUT AND PASTE RANGES OF CELLS

Create a new subroutine.

B Type Dim CutRange As Range, replacing CutRange with the variable containing the range to cut.

-0 Type Set CutRange = Range("B1:C5"), replacing Range("B1:C5") with the range to cut.

Create a new subroutine.

Note: See Chapter 3 for information on creating subroutines.

B Type Dim CutRange As Range, replacing CutRange with the variable containing the range to cut.

-0 Type Set CutRange = Range("B1:C5"), replacing Range("B1:C5") with the range to cut.

0 0

Post a comment