Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"deselect"
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"deselect"
Use InsertNameDefine 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"deselect"
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 InsertNameDefine 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"deselect"
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"deselect"
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 InsertNameDefine 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"deselect"
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"deselect"
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 InsertNameDefine 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"deselect"
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
"deselect"
thank again. exactly what I needed.
I'll keep it in mind for the future. "Dave Peterson" wrote: You could loop through the cells: |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
"deselect"
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: |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
"deselect"
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" wrote: 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: |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
"deselect"
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
"deselect"
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. "Héctor Miguel" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can someone tell me how to deselect "select all" in Excel? | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
deselect any "active" charts in workbook | Excel Programming | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
"deselect ?" a range or change state from edit to ? | Excel Programming |