How can I change range name definitions as the No. of rows change.
 

I import data from an accounting application. The columns are always
fixed at 7.. The number of rows will vary.

3 ranges are used in functions and a pivot tables.

ABC A1.C1 down to last row.
AtoG A1. G1 down to last row
FG F1.G1 down to last row.

The last row is the same for all 3 ranges.

Can anyone suggest the VBA code needed to accomplish this. I know
very little aboutr VBA . I just know enough to copy and paste new code
into code compiled by recording a macro.-

Thanks,

Harvey Snyder
Providence, RI

How can I change range name definitions as the No. of rows change.
 

Sub CreateNames()
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1").Resize(lastrow, 3).Name = "ABC"
Range("ABC").Resize(, 7).Name = "AtoG"
Range("ABC").Offset(0, 5).Resize(, 2).Name = "FG"
End Sub

just for demonstartion purposes, after running this I tested the results in
the immediate window:

? activeworkbook.Names("ABC").RefersTo
=Sheet1!$A$1:$C$27
? activeworkbook.Names("AtoG").RefersTo
=Sheet1!$A$1:$G$27
? activeworkbook.Names("FG").RefersTo
=Sheet1!$F$1:$G$27

Look good to me.

--
Regards,
Tom Ogilvy




wrote in message
oups.com...
I import data from an accounting application. The columns are always
fixed at 7.. The number of rows will vary.

3 ranges are used in functions and a pivot tables.

ABC A1.C1 down to last row.
AtoG A1. G1 down to last row
FG F1.G1 down to last row.

The last row is the same for all 3 ranges.

Can anyone suggest the VBA code needed to accomplish this. I know
very little aboutr VBA . I just know enough to copy and paste new code
into code compiled by recording a macro.-

Thanks,

Harvey Snyder
Providence, RI