RangeSize set by macro

[Guest]

Hi All.......

If someone would be so kind.....I am in need of a macro that will look to
A7, and then depending on how many consecutively populated cells are below
it, create RangeName "Data1" of that size. Then do the same thing for
RangeName "Data2" starting at cell B7. Then, combine the two as RangeName
"Data3" from A7 whatever becomes the bottom end cell of "Data2"........when
I do this by recording, it just hard-codes the cells and does not allow if
the range size changes.

Assuming Data in A7:B14, then

RangeName "Data1" would be assigned to A7:A14
RangeName "Data2" would be assigned to B7:B14
RangeName "Data3" would be assigned to A7:B14

TIA for any guidance.....
Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

set rng = Range(Range("A7"),Range("A7").End(xldown))
rng.name = "Data1"
rng.offset(0,1).name = "Data2"
rng.Resize(,2).name = "Data3"

I assume A and B will always end on the same row.

If that is not the case then:

set rng1 = Range(Range("A7"),Range("A7").End(xldown))
set rng2 = Range(Range("B7"),Range("B7").End(xldown))
set rng3 = Range(rng1, rng2)

rng1.Name = "Data1"
rng2.Name = "Data2"
rng3.Name = "Date3"

 

[JE McGimpsey]

You don't need programming, of course. It may be more efficient to use
dynamic ranges:

http://cpearson.com/excel/named.htm#Dynamic

But you can use something like:

Range(Cells(7, 1), Cells(Rows.Count, 1).End(xlUp)).Name = "Data1"
Range(Cells(7, 2), Cells(Rows.Count, 2).End(xlUp)).Name = "Data2"
Cells(7, 1).Resize(Range("Data2").Rows.Count,2).Name = "Data3"

or a bit more efficiently

Range(Cells(7, 1), Cells(Rows.Count, 1).End(xlUp)).Name = "Data1"
With Range(Cells(7, 2), Cells(Rows.Count, 2).End(xlUp))
.Name = "Data2"
Cells(7, 1).Resize(.Rows.Count,2).Name = "Data3"
End With

 

[Guest]

If there is no empty cell within the data in column A, you coan define your
Named Ranges (menu Insert > Name > Define) as:
Assuming there are 3 non-empty cells in A1:A6 (if 5, then change the '3' in
into a 5 in the formulas)
Data1
=OFFSET($A$7,0,0,COUNTA($A:$A)-3,1)
Data2
=OFFSET($A$7,0,1,COUNTA($A:$A)-3,1)
Data3
=OFFSET($A$7,0,0,COUNTA($A:$A)-3,2)

These named ranges are dynamic, therefore update automatically when you add
or delete data.

Regards,
Sebastien

 

[Guest]

Thank you very much Tom......

Your code is exactly what I was looking for in this instance, and even more
to "grow on".

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Thanks Sebastienm.............

I dunno what happened.........I know I wrote an answer to you before I left
work, but it's not showing up, so I guess the ether-gods ate
it...........anyway, it took several reads before I really understood what
you were suggesting, as the whole concept of putting formulas in as
reference for a RangeName is new to me. But cool it is, and most clever how
you accommodated my preferred range starting point of A7 rather than
A1.......... In the present case, Tom's code is what I went with, because
it worked and that was where my mindset was at the time, but I will for sure
keep this method in mind for the future........

Many thanks again............
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Thanks JE............

I went and read your reference on Chip's page and it's really unbelievable
how much more RangeNames can be used than what I was capable of. I've only
used them to reference cells, or groups of cells identified by their
row/column locations. Of course this suggestion is the way to go in the
future after I practice a bit. This time I went with Tom's code..........it
was there, and worked, and I sorta understood it.......three good things in
my book.

Thanks again for your response..........
Vaya con Dios,
Chuck, CABGx3