Areas

You need to be careful when using the Columns or Rows properties of non-contiguous ranges, such as those returned from the SpecialCells method when locating the numeric cells or blank cells in a worksheet, for example. Recall that a non-contiguous range consists of a number of separate rectangular blocks. If the cells are not all in one block, and you use the Rows.Count properties, you only count the rows from the first block. The following code generates an answer of 5, because only the first range, A1:B5, is evaluated:

Range("A1:B5,C6:D10,E11:F15").Rows.Count

The blocks in a non-contiguous range are Range objects contained within the Areas collection and can be processed separately. The following displays the address of each of the three blocks in the Range object, one at a time:

For Each

rng

In Range("A1:B5,C6:D10,E11:F15" :

1.Areas

MsgBox

rng.

.Address

Next rng

The worksheet shown in Figure 4-11 contains sales estimates that have been entered as numbers. The cost figures are calculated by formulas.

S j ffetigeptfcei r. (Bin

_ it

it

A

a c

E

F

1

1

2

Jan Feb

Mar

3

Salesl

100

120

130

4

5

Costl

Sol

72

78

6

7

Sales2

50

60

70

3

9

Cost2

30

36

42

10

11

Sales3

40

35

55

12

13

Cost3

24

21

33

14

15

16

F

M

► H

Araas Constant

3 _

"I'ÜPLl

ZM9H

Figure 4-11

The following code copies all the numeric constants in the active sheet to blocks in the sheet named Constants, leaving an empty row between each block:

Sub

CopyAreas()

Dim rng As Range, rngDestination As Range

'Copy the areas in a non-contiguous range

'Set the destination range

Set rngDestination = Worksheets("Constants").Range("A1")

'Process each non-contiguous area of numeric values

For Each rng In Cells.SpecialCells(xlCellTypeConstants, xlNumbers).Areas

rng.Copy Destination:=rngDestination

' Set next destination under previous block copied

Set rngDestination = rngDestination.Offset(rng.Rows.Count + 1)

Next rng

End

Sub

This gives the result shown in Figure 4-12.

%J Ranged bjectflSITi

_ ci

A B C D

e

-

1 2

100 120 13D

3

SO 60 70

4

5

40 35 55

6

7

=

?

3

10

11

12

13

14

15

16

n ^

* h Areas Constants

ji^Nii i 43

0 0

Post a comment