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

Was this article helpful?

0 0
Black Jack Tactics Conquering The Game In Casinos

Black Jack Tactics Conquering The Game In Casinos

So, you want to learn how to play Blackjack. Youre in good hands. There is no better ship to chart those learning waters than the one youre riding on right now.

Get My Free Ebook


Post a comment