Current Region Property

If you have tables of data that are separated from surrounding data by at least one empty row and one empty column, you can select an individual table using the CurrentRegion property of any cell in the table. It is equivalent to the manual Ctrl+* keyboard shortcut (or Ctrl+A). In the Figure 4-7 worksheet, you could select the Bananas table by clicking the A9 cell and pressing Ctrl+*.

f&ngetJÖfaef.iTsim

_ n

A

B

C

Ó

E S

:

Amalgamated Fruit Inc.

3 ¡Mangoes

Jan

Feb

Mar

i

South

6666

7345

5364

5 1 North

1204

3236

4212

6 ;East

841

733

70 58

7

West

5559

4311

5280

8

9

Bananas

Jan

Feb

Mar

10

South

436

6539

8166

11

North

4175

6362

4026

12

East

2568

9731

9735

=

13

West

3135

1312

ES 1

14

15 Lychees

Jan

Feb

Mar

16 ¡South

7052

905

6428

17

North

3964

11G

6013

16

East

9256

1434

6409

19

West

1684

B703

4412

20

21

Rambutan

Jan

Feb

Mar

22

South

8068

8683

4976

23 'North

7480

565

21

24 East

8190

7642

4088

25

West

1659

4444

5905

26

1

m

► W Sales Consolidaoorr .

Figure 4-7

The same result can be achieved with the following code, given that cell A9 has been named Bananas:

Range("Bananas").CurrentRegion.Select

This property is very useful for tables that change size over time. You can select all the months up to the current month as the table grows during the year, without having to change the code each month. Naturally, in your code, there is rarely any need to select anything. If you want to perform a consolidation of the fruit figures into a single table in a sheet called Consolidation, and you have named the top-left corner of each table with the product name, you can use the following code:

Sub Consolidated

Dim vProducts As Variant

Dim rngCopy As Range 'Range to be copied

Dim rngDestination As Range

Dim iProductlndex As Integer

'Sum each product to give consolidated result Application.ScreenUpdating = False vProducts = Array("Mangoes", "Bananas", "Lychees", "Rambutan")

Set rngDestination = Worksheets("Consolidation").Range("B4")

For iProductlndex = LBound(vProducts) To UBound(vProducts)

With Range(vProducts(iProductIndex)).CurrentRegion 'Exclude headings from copy range

Set rngCopy = .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1) End With rngCopy.Copy

If iProductlndex = LBound(vProducts) Then 'Paste the first product values rngDestination.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone Else

'Add the other product values rngDestination.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd End If

Next iProductlndex

Application.CutCopyMode = False 'Clear the clipboard End Sub

This gives the output in Figure 4-8.

® * ftsng-epbrei t.lSBW

- -

A

BCD

E M

1

Amalgamated Fruit Inc.

2

3

Consolidation

Jan Feb Mar

-t

South

22222 23472 24934

=

5

North

16903 10281 14272

6

East

2ÛS55 19540 28390

7

West

12047 18775 16258

3

10

11

1'

n

» M Sates

Consolidation J

Screen updating is suppressed to cut out screen flicker and speed up the macro. The Array function is a convenient way to define relatively short lists of items to be processed. The LBound and UBound functions are used to avoid worrying about which Option Base has been set in the declarations section of the module. The code can be reused in other modules without a problem.

The first product is copied and its values are pasted over any existing values in the destination cells. The other products are copied and their values added to the destination cells. The clipboard is cleared at the end to prevent users accidentally carrying out another paste by pressing the Enter key.

0 0

Post a comment