Working with Named Ranges

The spreadsheet in Figure 5-5 contains a data list in B4:D10 that has been given the name Database. There is also a data input area in B2:D2 that has been given the name Input.

CÏÎT ^

»

MeniiSijI

m -

3' f*.

H&l ; IllFt P

c Fe

Dat 1 F h

S1

_ itt v

input

-

ri 1

Shelly

¥

K

B

c

D

E

l

1

Name

flqe

Sex

2

13 h e 11 v

2G F

3

4

Name

Aqe

Sex

S

John

21 M

6

Mary

23 F

7

Harry

1G M

B

Jack

2B M

9

Elizabeth

43 F

10

□an

SE M

11

12

13

14

IS

h

» H

Named Ranges

NJI

H. -m

JHi

•Vtrage:

Count: 3

jum; Z6

Jsi

i J

Figure 5-5

If you want to copy the Input data to the bottom of the data list and increase the range referred to by the name Database to include the new row, you can use the following code:

Sub AddNewData()

Dim lRows As Long

'Copy data & Extend range of Database by one row

With Range("Database")

Range("Input")

.Copy

Destination:=.Cells(lRows, 1)

.Resize(lRows)

.Name

= "Database"

End With

End Sub

The output resulting from this code will be the same as Figure 5-5, but with the data Shelley 26 F in cells B11:D11. The range Database will now refer to B4:D11.

The variable lRows is assigned the count of the number of rows in Database plus 1, to allow for the new record of data. Input is then copied. The destination of the copy is the B11 cell, which is defined by the Cells property of Database, being lRows down from the top of Database in column 1 of Database. The Resize property is applied to Database to generate a reference to a Range object with one more row than Database, and the Name property of the new Range object is assigned the name Database.

The nice thing about this code is that it is quite independent of the size or location of Database in the active workbook and the location of Input in the active workbook. Database can have seven rows or 7,000 rows. You can add more columns to Input and Database and the code still works without change. Input and Database can even be on different worksheets and the code will still work.

0 0

Post a comment