Returning a Portion of a Pivot Table

Several of the members of the PivotTable object are designed to return a portion of the pivot table as a Range object. They are as follows.

20.5.3.1 ColumnRange property

This property returns a Range object that represents the column area in the pivot table. Figure 2011 illustrates the column range.

Figure 20-11. The ColumnRange range

A

C

C

D

E

F

G

1

Year

(Alii

2

3

1

4

Store City

Store Type

Data

5

BOSTON

Company

Sum of Transactions

2824Ö

20714

28672

2860 2

6

Sum of Sales

44*73

46256

1

Franchise

Sum cl 1 "arrjactions

13933

13942

132 'ti

13210

3

Sum of Sale-:-

21316

21739

1363 J

18325

9

LÖS ANGE

Company

Sum of Transactions

345S8

35B3S

35692

350'j 1

10

Sum of Salci.

123478

12Ô564

126340

12541S

11

Franchise

Sum '^f Transactions

71583

72947

7 5619

74392

12

Sum of Salsç

2É2431

2382^4

279325

274531

15

NEW VOR

Company

Sum i^f Transactions

24616

26104

27015

26854

14

Sum of Sales

7R089

«7905

8805fi

15

Franchise

Sum of Transactions

53273

54351

51Ö22

51218

16

Sum of Sales

176353

170537

1^7446

20.5.3.2 DataBodyRange property

This property returns a Range object that represents the PivotTable's data area. Figure 20-12 shows the results of selecting the DataBodyRange.

Figure 20-12. The DataBodyRange range

A

B

S

O

E 1

F

G f

1

Year (All) w\

?

3

Period

4

Store City I

Störe TypM

Cal3

:

3

4|

5

BOSTON

Company

Sun or Transactions

23248

28714

23672

23602I

6

Sun or Sales

440/8

4692/

462ti(i

46223

7

Franchisé

Sum of Transactions

13SÖ3

13942

13275

13210

3

Sun of igfes

21S1C

21739

18632

I Si 25

9

LOSANGE

Ccmsany

Sun of TranTactions

34^ 88

3:.933

35692

35CC '

10

Sun or Sales.

'23470

123564

126340

125-118

11

Franchise

Sun of Transactions

71583

72947

75619

74392

12

Sun or Sales

26243'

268274

279325

27453'

13

NEW Y OR

Coinpany

Sur i o! Tiarisadio'ib

24611?

23-i04

27015

14

Sun of Sale 5

78C09

S 5251

37905

38C53

10

Früi :iro

Sun of Transactions

53.?73

5435 1

513 22

51213

16

Sun of iiale?

■ 76??3

182461

1705.-7

16744 e|

20.5.3.3 DataLabelRange property

This read-only property returns a Range object that represents the labels for the PivotTable data fields. Figure 20-13 illustrates DataLabelRange.

Figure 20-13. The DataLabelRange range

A

B

C

D

E

F

G

1

Year

(W____-

;

|Period |

4

Store City

Store Typ^

| Data

1

2

3

4

6

BOSTON

Company

2824 R

14

2807?

23607

5

4467ft

4S927

<1*256

46223

7

Franchise

Strr of Transactions

13393

13S42

13275

13210

3

Star ot Sales

2181 ES

21733

18632

1S325

9

L OS AN3E

C ornpany

[SLrr of Transactions

34538

35Ô3S

35092

3^00 1

10

Strr of Galas

12347 8

123564

12634 0

Î25418

11

rranc hiss

Slht of Transact; on;

71505

72947

75619

743'j2

12

ouir of Sal^i

262431

263274

279325 274531|

13

NEW Y OR

Company

Strfn of Transactors

24016

25' 04

27015

26 S 54

14

SLrr of Sale-:

730SÔ

3525 1

07905

8&058

15

Franchise

SLrr of Transactions

63^73.

54351

5 1822

51218

16

Strr of Sal^f

176353

132461

170537

167446

20.5.3.4 PageRange and PageRangeCells properties

The PageRange property returns a Range object that represents the PivotTable's page area. This is the smallest rectangular region containing all page field-related cells.

The PageRangeCells property returns a Range object that represents just the cells in the PivotTable containing the page-field buttons and item drop-down lists. Figure 20-14 and Figure 20-15 illustrate the difference.

Figure 20-14. The PageRange range

1

Year

[ij I ^^H ▼ H

■■Ml

S

H v

g

4

?

Penod

6

Data

1

2

3

4

Grand Total

7

StffTi of Transactons

226301

:s 1996

232095

229277

919609

fi

3 .Tl ' if SalSS

7 mavis

734216

7

720001

?ÎÎ90057

Figure 20-15. The PageRangeCells range

1

2

Year

(AJI) |

L

_B

M

3

(All) I

i

5]

I Period

6

Date

1

2

3

4

Grind Total

7

Sum of Transactions

220301

231390

232095

22317T

9ÎS669

r.

R-IJÎII nf

706ÎV15

7/1-1 ? 16

7289G6

7?n(]01

20.5.3.5 RowRange property

This property returns a Range object that represents the PivotTable's row area. Figure 20-16 illustrates the row area.

Figure 20-16. The RowRange range

Data

Sum of Sun of Sut of Sum of

Sun of Su n of Sun Of Sut of

Transactions Sales

Data

Sum of Sun of Sut of Sum of

Sun of Su n of Sun Of Sut of

Transactions

Sales_

Transactions

Sales_

Transactions

Sales

Transactions 53I&5

Transactions Sales

Transactions Sales

20.5.3.6 TableRange1 property

This property returns a Range object that represents the entire PivotTable except the page fields. This is illustrated in Figure 20-17.

Figure 20-17. The TableRange1 range

A

B

c

E

F

G

1

Y&ar

(All; T

2~1

J

■■i

*

m^mÊÊÊÊm

1

2

3

4

S

BOSTON

Company

¿u'n of Transactions

23714

2ÖÖ72

2SS02

6

Su n of Sales

44Ë7S

41^27

¿6253

7

Franchise

Sut of Transactions

13-993

13942

'3275

8

Sut of SélèS

: I f■ ■ e

21739

' 6032

13-^25

9

LOS ANGE

Coripgny

Su m of Transact! ons

34GS0

3G93S

35G32

35001

' 10 '

Sum of Ssteï

123470

129564

1263411

134 IS

11

F-anohiS-e

Sun of Transactions

71553

72947

75i?13

7*1302

Sum of Sales

2?2431

25Ö274

279325

274531

13

Company

Sun of Transactions

346'6

215104

27(:15

2G854

14

¿un of Sales

78089

35251

Ö7G05

8S05*

16

F' ichise

Smrt oi Transactions

53273

54351

10

Sut of Sales

175353

132461

1 -

20.5.3.7 TableRange2 property

This property returns a Range object that represents the entire PivotTable, including the page fields. This is illustrated in Figure 20-18.

Figure 20-18. The TableRange2 range

A

B

C

D

E

F

I <Li

1

Year

I All C

2

3

4

1

?

3

4

n

BOSTON

Convoy

SLirn o" Transsctors

282-13

2871-1

28672

29602

o

Sunn <-/ Gates

44670

46927

46256

46223

7

Fran.; hiss

Sunn Transactors

13993

13942

13275

13210

e

Sunn of Sales

21316

21739

1S632

1S3-35

9

Sunn of Transactors

3593 3

35632

10

Sunn oJ ¿ales

IEB

129564

126340

11

Franchise

Sunn of Transactors

ME

E3

72947

756 19

mm

12

Sum of Salts

ED

268274

27^325

13

Sum pJ Irani-actors.

24616

26104

2'(J1E>

26S54

14

Sum of Safes

7S039

35251

3S05S

15

Fran;hiS-&

Sum □' "ran-i-actors

53373

54351

5 1322

5121S

16

Sum SaU:

176353

152431

1705 =-7

167446

0 0

Post a comment