"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. |
"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. |
"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. |
"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. |
"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. |
"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 |
"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. |
"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 |
"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: |
"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: |
"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: |
"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. |
"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. |
All times are GMT +1. The time now is 08:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com