Resize Property

You can use the Resize property of the Range object to refer to a range with the same top left-hand corner as the original range, but with a different number of rows and columns. The following refers to D10:E10:

Range("D10:F20").Resize(1,2)

Resize is useful when you want to extend or reduce a range by a row or column. For example, if you have a data list, which has been given the name Database, and you have just added another row at the bottom, you need to redefine the name to include the extra row. The following code extends the name by the extra row:

With Range("Database")

.Resize(.Rows.Count + 1).Name =

"Database"

End With

When you omit the second parameter, the number of columns remains unchanged. Similarly, you can omit the first parameter to leave the number of rows unchanged. The following refers to A1:C10:

You can use the following code to search for a value in a list and, having found it, copy it and the two columns to the right to a new location. The code to do this is:

Sub

FindIt()

Dim rng As Range

'Find data and use Resize property to

copy range

Set rng = Range("A1:A12").Find(What:='

' Jun", _

LookAt:

:=xlWhole, LookIn:=xlValues)

If rng Is Nothing Then

MsgBox "Data not found"

Exit Sub

Else

rng.Resize(1, 3).Copy Destination:

:=Range("G1")

End If

End

Sub

And the result is shown in Figure 4-5.

RsiigePti/ecMsm

T

A

e c

D E

F I E

H

1 1 i

I

1 Uan

100

Jun

132

131

2 I Fab

123

3 Mar

115

113

4 Apr

140

12S

S ¡May

120

125

G lJun

132

131

=

7 ¡Jul

124

125

B lAug

120

125

S ¡Sep

1ÏE

120

10lGcI

132

122

J1¡Nov

143

130

12 'Dec

128

134

13

14 I

1_S|

it .

Il i ► n HIICBlls CcbrCälS

MovingAuerage

Iii 1 1_

Figure 4-5

The Find method does not act like the Edit OFind command. It returns a reference to the found cell as a Range object, but it does not select the found cell. If Find does not locate a match, it returns a null object that you can test for with the Is Nothing expression. If you attempt to copy the null object, a run-time error occurs.

0 0

Post a comment