Hi.

I have a range name that defines a LOT of discontinuous cells... probably
more than 100.

But unfortunately, there is one cell, BR8 , included in this range name
definition, which should not be.

I've used the .Address and the .RefersTo properties to try to get the
addresses of all of the cells define. But, the length of the properties must
not be long enough to handle the definitions.

I can use F5 and go to the discontinuous range, but I need a way to
"deselect" the one cell that should not be there, so that I can then redefine
the range name to be the current selection.

Help?

Thanks.

Use Insert>Name>Define to bring up the dialog box in Excel. Then you can
modify the range in the Refers To: window.

"mark" wrote:

> Hi.
>
> I have a range name that defines a LOT of discontinuous cells... probably
> more than 100.
>
> But unfortunately, there is one cell, BR8 , included in this range name
> definition, which should not be.
>
> I've used the .Address and the .RefersTo properties to try to get the
> addresses of all of the cells define. But, the length of the properties must
> not be long enough to handle the definitions.
>
> I can use F5 and go to the discontinuous range, but I need a way to
> "deselect" the one cell that should not be there, so that I can then redefine
> the range name to be the current selection.
>
> Help?
>
> Thanks.

tried that earlier. doesn't seem to work.

it only shows me the first line of the definitions, which only goes through
the fifth cell.

if I try to arrow right, it picks up new cell addresses where the cursor
currently is, but I haven't been able to get it to move to the cell that I
need to remove, in the range name definition.

"JLGWhiz" wrote:

> Use Insert>Name>Define to bring up the dialog box in Excel. Then you can
> modify the range in the Refers To: window.
>
> "mark" wrote:
>
> > Hi.
> >
> > I have a range name that defines a LOT of discontinuous cells... probably
> > more than 100.
> >
> > But unfortunately, there is one cell, BR8 , included in this range name
> > definition, which should not be.
> >
> > I've used the .Address and the .RefersTo properties to try to get the
> > addresses of all of the cells define. But, the length of the properties must
> > not be long enough to handle the definitions.
> >
> > I can use F5 and go to the discontinuous range, but I need a way to
> > "deselect" the one cell that should not be there, so that I can then redefine
> > the range name to be the current selection.
> >
> > Help?
> >
> > Thanks.

ahhh, I got it.

this is a template, and there are only a few rows in the worksheet.

if I delete column BR, then re-insert it, and redefine the things that
should be there, it will remove BR8 from the range name.

but it seems like there ought to be a programmatic way available.

is there?

"mark" wrote:

> tried that earlier. doesn't seem to work.
>

The only other way I know is to completely redo the name and omit that one.
You can do it manually in Excel, or use VBA by:

myRange = Range("A1, B5.....IX750") 'example only to establishe the
'non-contiguous range.
myRange.Name = "SomeName" 'example to assign the range name

good luck.

"mark" wrote:

> tried that earlier. doesn't seem to work.
>
> it only shows me the first line of the definitions, which only goes through
> the fifth cell.
>
> if I try to arrow right, it picks up new cell addresses where the cursor
> currently is, but I haven't been able to get it to move to the cell that I
> need to remove, in the range name definition.
>
> "JLGWhiz" wrote:
>
> > Use Insert>Name>Define to bring up the dialog box in Excel. Then you can
> > modify the range in the Refers To: window.
> >
> > "mark" wrote:
> >
> > > Hi.
> > >
> > > I have a range name that defines a LOT of discontinuous cells... probably
> > > more than 100.
> > >
> > > But unfortunately, there is one cell, BR8 , included in this range name
> > > definition, which should not be.
> > >
> > > I've used the .Address and the .RefersTo properties to try to get the
> > > addresses of all of the cells define. But, the length of the properties must
> > > not be long enough to handle the definitions.
> > >
> > > I can use F5 and go to the discontinuous range, but I need a way to
> > > "deselect" the one cell that should not be there, so that I can then redefine
> > > the range name to be the current selection.
> > >
> > > Help?
> > >
> > > Thanks.

You could loop through the cells:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim myRngToSkip As Range

With Worksheets("sheet1")
'add/subtract addresses here!
Set myRngToSkip = .Range("BR8,F6")

For Each myCell In .Range("Name1").Cells
If Intersect(myCell, myRngToSkip) Is Nothing Then
If myRng Is Nothing Then
Set myRng = myCell
Else
Set myRng = Union(myRng, myCell)
End If
Else
'skip it, it's in the range to skip
End If
Next myCell

If myRng Is Nothing Then
MsgBox "No cells!"
Else
myRng.Select 'just for testing!
myRng.Name = "Name2" 'or even Name1 if you want to reuse that name.
End If
End With

End Sub



mark wrote:
>
> Hi.
>
> I have a range name that defines a LOT of discontinuous cells... probably
> more than 100.
>
> But unfortunately, there is one cell, BR8 , included in this range name
> definition, which should not be.
>
> I've used the .Address and the .RefersTo properties to try to get the
> addresses of all of the cells define. But, the length of the properties must
> not be long enough to handle the definitions.
>
> I can use F5 and go to the discontinuous range, but I need a way to
> "deselect" the one cell that should not be there, so that I can then redefine
> the range name to be the current selection.
>
> Help?
>
> Thanks.

--

Dave Peterson

Disregard the VBA code in the previous message. The correct methor for
adding a range name is:

Names.Add Name:="SomeName", RefersTo:="=sheet1!$a$1, sheet1!$b$6,..."

I slip off into senility occasionally. HTH



"mark" wrote:

> tried that earlier. doesn't seem to work.
>
> it only shows me the first line of the definitions, which only goes through
> the fifth cell.
>
> if I try to arrow right, it picks up new cell addresses where the cursor
> currently is, but I haven't been able to get it to move to the cell that I
> need to remove, in the range name definition.
>
> "JLGWhiz" wrote:
>
> > Use Insert>Name>Define to bring up the dialog box in Excel. Then you can
> > modify the range in the Refers To: window.
> >
> > "mark" wrote:
> >
> > > Hi.
> > >
> > > I have a range name that defines a LOT of discontinuous cells... probably
> > > more than 100.
> > >
> > > But unfortunately, there is one cell, BR8 , included in this range name
> > > definition, which should not be.
> > >
> > > I've used the .Address and the .RefersTo properties to try to get the
> > > addresses of all of the cells define. But, the length of the properties must
> > > not be long enough to handle the definitions.
> > >
> > > I can use F5 and go to the discontinuous range, but I need a way to
> > > "deselect" the one cell that should not be there, so that I can then redefine
> > > the range name to be the current selection.
> > >
> > > Help?
> > >
> > > Thanks.

yeah, that would work.

Thanks guys!

I just did my delete column, but then I had to go through and re-insert the
formulas, and edit the range name where the cell is properly called out.

thanks for the help.

"Dave Peterson" wrote:

> You could loop through the cells:
>
> Option Explicit
> Sub testme()
> Dim myRng As Range
> Dim myCell As Range
> Dim myRngToSkip As Range
>
> With Worksheets("sheet1")
> 'add/subtract addresses here!
> Set myRngToSkip = .Range("BR8,F6")
>
> For Each myCell In .Range("Name1").Cells
> If Intersect(myCell, myRngToSkip) Is Nothing Then
> If myRng Is Nothing Then
> Set myRng = myCell
> Else
> Set myRng = Union(myRng, myCell)
> End If
> Else
> 'skip it, it's in the range to skip
> End If
> Next myCell
>
> If myRng Is Nothing Then
> MsgBox "No cells!"
> Else
> myRng.Select 'just for testing!
> myRng.Name = "Name2" 'or even Name1 if you want to reuse that name.
> End If
> End With
>
> End Sub
>
>
>
> mark wrote:
> >
> > Hi.
> >
> > I have a range name that defines a LOT of discontinuous cells... probably
> > more than 100.
> >
> > But unfortunately, there is one cell, BR8 , included in this range name
> > definition, which should not be.
> >
> > I've used the .Address and the .RefersTo properties to try to get the
> > addresses of all of the cells define. But, the length of the properties must
> > not be long enough to handle the definitions.
> >
> > I can use F5 and go to the discontinuous range, but I need a way to
> > "deselect" the one cell that should not be there, so that I can then redefine
> > the range name to be the current selection.
> >
> > Help?
> >
> > Thanks.
>
> --
>
> Dave Peterson
>

thank again. exactly what I needed.

I'll keep it in mind for the future.

"Dave Peterson" wrote:

> You could loop through the cells:
>

hi, Mark !

you have anther approach in your first post

hth,
hector.

> thank again. exactly what I needed.
>
> I'll keep it in mind for the future.
>
> "Dave Peterson" wrote:
>
>> You could loop through the cells: