Add a name to selected cells

[jgmiddel12344]

In a sheet I have a variable selection of cells. I want to add a name
(overviewselection) to these cells with a macro. How should I do that?

 

[Don Guillett]

More detail
"Hook em horns"

 

[jgmiddel12344]

The selected cells are on a sheet "Overview". The selection van be B4:N5 and
anything more (B4:N6, B4:N7), etc. I have to sort these rows and I think that
naming the range is a good way to do it.

 

[jgmiddel12344]

BTW, can also be the range B4:O5, etc.

 

[Bernie Deitrick]

Visit

http://www.cpearson.com/excel/named.htm

and look at the part concerning dynamic named ranges.

HTH,
Bernie
MS Excel MVP

 

[Don Guillett]

Goto the sheet desired>insert>name>define>name it srtrng>in the refers to
box type or copy this.
Now, in the absense of blanks, the range will auto adjust to additional rows
in col B or columns in row 4
=OFFSET($B$4,0,0,COUNTA($B:$B)-3,COUNTA($4:$4)-1)
use the name for your sortrange.

 

[Chip Pearson]

Try code like

If TypeOf Selection Is Excel.Range Then
ActiveWorkbook.Names.Add _
Name:="OverviewSelection", RefersTo:=Selection
End If


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)

 

[jgmiddel12344]

Hmm, very weird. This works fine in a new workbook, but not in my project.
In my project the capital of "Selection" turns into a normal letter. What can
I do wrong?

Thanks in advance

 

[Dave Peterson]

The upper/lower case of the S in selection won't matter to the running of the
code. You may want to explain what happened in the troublesome project.

If you want to fix that upper/lower case problem:
Find an empty line in your procedure:
Type
Dim Selection
and hit enter.

Then delete that line and try typing the original code again.