Listing Calling Current Region to Inspect a Lists Useful Characteristics

Sub CurrentRegionExample() Dim rg As Range Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Current Region")

' get current region associated with cell A1 Set rg = ws.Ce11s(1, 1).CurrentRegion

' number of header rows ws.Range("I2").Va1ue = rg.ListHeaderRows

' number of columns ws.Range("I3").Va1ue = rg.Columns.Count

' resize to exclude header rows Set rg = rg.Resize( _

rg.Rows.Count - rg.ListHeaderRows, _ rg.Co1umns.Count).Offset(1, 0)

' number of rows ex header rows ws.Range("I4").Va1ue = rg.Rows.Count

' number of cells ex header rows ws.Range("I5").Va1ue = rg.Cells.Count

' number empty cells ex header rows ws.Range("I6").Va1ue = Application.WorksheetFunction.CountBlank(rg) ' number of numeric cells ex header rows ws.Range("I7").Va1ue = Application.WorksheetFunction.Count(rg) ' last row ws.Range("I8").Va1ue = rg.Rows.Count + rg.Ce11s(1, 1).Row - 1

Set rg = Nothing Set ws = Nothing End Sub

This procedure demonstrates a number of range properties and a property of the Application object that I don't believe you've seen yet. The important statement, for the purposes of this example, is the one that sets the rg variable to refer to the current region associated with cell "A1." The List-HeaderRows property returns the number of rows that appear to be header rows. As you can see, you can use the value of this property to resize your Range object so that it represents data rows only.

The only other statements of interest are the ones that use the WorksheetFunction property of the Application object. This property provides programmatic access to the standard worksheet functions that you can use from a worksheet.

The output of the CurrentRegionExample is shown in Figure 9.11. The list is the current region associated with cell A1. The characteristics of the list are output beginning with cell H1.

Figure 9.10

A range of data for experimenting with CurrentRegion.

Figure 9.11

Output of the CurrentRegion-Example procedure

File Edt

View Insert

Format Tools

Dal-3 Window

Help 1 Type a question for help

5 X

i z

' ft ;

Ariel

i i0 -

D /

B i

s

a a % . Jl ^Lfiffi il

• A'

A

- 1

;

jy J|U

i x : ^ ■

II

1 G

1 1

A

B

1 c

0

i

1

m

H

~n

J Ä1

1

trans id

product

region

sales

list properties

2

16

STD

SOUTHWEST

433

Number Header Rows

=i

!

3

15

STD

SOUTHERN

332

Number of columns

4

14

STB

ROCKY MTtl

569

Number of records (ex header)

15

5

13

NORTHEAST

456

Number of cells (a* header)

64

B

12

STD

NORTHEAST

ess

Number of empty cells (ex header)

7

11

STD

NORTHEAST

Number of (tumeric cells (ex headsr)

30

S

10

STO

NORTHEAST

2345

Las! Row

17

9

9

STD

MIDWEST

099

10

B

DLX

UNKNOWN

2245

11

7

DLX

WESTERN

657

12

E

WESTERN

1433

13

5

DLX

SOUTHWEST

654

14

4

DLX

SOUTHWEST

15

3

DU

NORTHEAST

774

16

2

DLX

NORTHEAST

76

i 17

1

DLX

MIDWEST

443

1B

19

20

21

M 1

» ttft Current Reolon/a>edal Cells / sheer:, / rotting

7"

1

>J

I B^f

1 Microstoil Oïfile Exit! Z003 Beta - Clidtler 9 EKamou.

ÏIM] File Edit Vieri Cnscrt Format Tools Dato Window

! Beb - 5

X

i " B ; n l S 2 S ' -ô! ¿S. i- -A'

È

: _J il ¿i flj

i

F12 - f*

A

B

C

D

E

. A

1

2

1

1

1

3

1

1

1

4

1

1

1

5

S

1

1

1

7

1

1

a

1

1

1

g

10

1

il

1

1

1

12

1

~~I

13

i14

V

»II niysheeto / SnWm. l< > ]

la»

0 0

Post a comment