AutoNameing a range from a cell

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
Right I think I can describe what I want to do but bear with me.

I have a column with 11 values in it, say a10 to a21. In a10 I have the string "JJcost" and in a11 to a21 i have the numbers 1 to 10.

What I want to do is select any cell between a10 and a21 and then run the macro which will look to the first cell a10 in this case and assign its value to be the name of the range ie "JJCost"

Can anyone point me in the right Direction?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
... the first cell a10 in this case and assign its value to be the name of the range ie "JJCost"

If you select the range A10:A21 and press Ctrl-Shift-F3, that will show the Create Names dialog and put a default of Top Row as the guess, so you just press Enter, and the range ( A11:A21 ) is named "JJCost" in this case.
 

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
Ok thanks for that but It it doesn't work out top and bottom of the range. IE I click on a15 and run the macro and it will find the firstcell and the last cell with data in that column and range and work it out.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Can I ask, why are you being insistent on using a macro?

Ctrl-* will select all cells in the current region. So if you click on A15, do Ctrl-* then do Ctrl-SHift-F3 and Enter, that will do exactly what you want, unless the range A10:A21 is not a separate range of data.
 

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89

ADVERTISEMENT

The reason I want to use a macro is I intend puting it into a addon I'm tring todo. And this is just part of the whole solution. Ie I'll be doing further things to the range once its defined.


From Your help I've got the following code which so far does what i want it to do .

Code:
Selection.CurrentRegion.Select
Selection.CreateNames Top:=True

One thing thou the Selection.CurrentRegion.Select selects the whole region rather than just the column and pointers?
 

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
Also I've just thought about it Is there a way i can from the code find out what names the ranges have been given.

As I'll need to do calculations on each range and put it into the correct column.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

... that will do exactly what you want, unless the range A10:A21 is not a separate range of data.

One thing thou the Selection.CurrentRegion.Select selects the whole region rather than just the column and pointers?
... yes, I'd said that in my post.

If you want to select the current column from current cell position, use
Range(ActiveCell.End(xlUp),ActiveCell.End(xlDown))

Also I've just thought about it Is there a way i can from the code find out what names the ranges have been given.

As I'll need to do calculations on each range and put it into the correct column.
... describe exactly what you are trying to do.
 

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
Right What i want to be able to do is Select say "b12" and from that it will make b11 to b17 into a range called COLa then populate b4,b5,and b6 with the formula. Does that make sense?

Also I would have 2 macros one for just the column and the other to do a complete range ie cola colb and colc with the relevant formulas at the top.
Book2
ABCD
1
2
3
4=Max(COLa)=Max(COLb)=Max(COLc)
5=Average(COLa)=Average(COLb)=Average(COLc)
6=Sum(COLa)=Sum(COLb)=Sum(COLc)
7
8
9
10COLaCOLbCOLc
11121
12222
13334
144354
15533
16642
17721
18132
Sheet1
 

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
I've finally amnaged to get then html generator working so i've edited the last post to include a sheet that should help
 

Forum statistics

Threads
1,136,878
Messages
5,678,306
Members
419,754
Latest member
LordEddard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top