Add a name to selected cells

  • Thread starter Thread starter jgmiddel12344
  • Start date Start date
J

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?
 
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.
 
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.
 
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)
 
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
 
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.
 
Back
Top