"deselect"

[mark]

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.

 

[JLGWhiz]

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

 

[mark]

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.

 

[mark]

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?

 

[JLGWhiz]

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.

 

[Dave Peterson]

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

 

[JLGWhiz]

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]

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.

 

[mark]

thank again. exactly what I needed.

I'll keep it in mind for the future.

 

[Héctor Miguel]

hi, Mark !

you have anther approach in your first post

hth,
hector.

 

[mark]

not sure what you're trying to tell me.

the approach in my "first post", or the "first response", either one, didn't
work.

 

[Héctor Miguel]

hi, Mark !

not sure what you're trying to tell me.
the approach in my "first post", or the "first response", either one, didn't work.

i'm sorry... by "first post" i meant your other thread: "opposite of Union"

hth,
hector.

 

[mark]

okay, thanks, Hector.

I tried to post the "opposite of Union" one just before I left work last
night.

But then later, after I got home, I couldn't find that post at all. Not
sure what happened?

Since I couldn't find the first one in the listing of posts, that's why I
wrote the second one.

Thanks, I see both now.