PDA

View Full Version : [SOLVED] How make defined range move upon sort?



TheAntiGates
12-18-2013, 11:48 AM
Perhaps the answer to this is not specifically VBA...

Assume cell B2 is named MyRange ( that is, in code, range("myrange") ) and its cell content is "foo"
I go Selection.sort
Now MyRange is still B2, but instead I want MyRange to move to wherever "foo" got sorted to.
If B9 is now where "foo" is, I want MyRange to now refer to B9, not B2

Is this possible? Can MyRange be dynamic is this regard?

(By the way, for grins I tried removing the $ off what MyRange refers to, literally making it "dynamic" as I understand the normal use of that term, but upon sorting MyRange seems to refer to A1, and is apparently an invalid range anyway. E.g. you can't "F5" to it. )

Here's the sort in case the parms matter Selection.Sort _
Key1:=Range("this"), Order1:=xlAscending, _
Key2:=Range("that"), Order2:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal

Zack Barresse
12-18-2013, 01:13 PM
Hi,

Even if you sort a named range, the name will still refer to the same range, so referencing it should still be the same...

SheetCodeName.Range("MyRange")

Sorted, filtered, doesn't matter.

TheAntiGates
12-18-2013, 03:30 PM
Well that sucks. How about this strategy:- dim a variant X and fill it with MyRange.value
- MyRange.value = <something very unique>
- sort
- set c=activesheet.Find(...
- c.Name = "MyRange"
- c.value=XIs that clear? (never mind that it is atrocious)

Zack Barresse
12-18-2013, 04:24 PM
I'm not sure I follow. Isn't it a good thing the name doesn't change? Do you need it to not change? If so that's fine, you can change the range which defines it to something static. Perhaps if you could elaborate a bit on the full process of what you're trying to accomplish it would help clear things up.

TheAntiGates
12-18-2013, 04:48 PM
Thanks for looking at this, Zack. Can anyone else comment on what I've done in the two posts above?

As far as your questions go, yes, I am in fact trying to do exactly what the thread title and first post asked. I believe that my second post is a valid solution, crafted in desperation despite no one saying that it was even possible. Now I'm asking if anyone has a better solution. So, yes, I am trying to make the defined range move upon sort.

If that abbreviated-/pseudo- code is not real obvious to anyone I'll formalize an official routine from it.

TheAntiGates
12-18-2013, 05:56 PM
Actually BTW - I noted it above - if you sort a named range, the name does not still refer to the same range if the referred to range is not absolute. Correcting my earlier thought, such bizarre range naming would not be the typical "dynamic range;" Dynamic ranges seem to always be described as using OFFSET or (ugh!)INDIRECT. I'm not sure, but just taking off the absolute $ anchors from a named range definition is probably a silly practice; I just tried that for grins.

Possible solution:Dim X as Variant, c as range
X = range("MyRange").value
range("MyRange").value = "PelosiPelosiBobosi"
Selection.Sort _
Key1:=Range("this"), Order1:=xlAscending, _
Key2:=Range("that"), Order2:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
Set c=activesheet.Find("PelosiPelosiBobosi")
c.Name = "MyRange"
c.value=X

Zack Barresse
12-18-2013, 06:12 PM
I'm really not sure I follow. Usually a defined name does use absolute cell referencing (utilizing the $ signs), and should probably be left this way by default. I assumed you had those in place, but you are correct, a defined name (absolute) will not follow on sorting. Sorry if I misspoke. But what happens if you have duplicate values in the sort range? If you want the range name to move, is there logic behind it?

What I meant by dynamic was probably not worded appropriately, and I should have said by a static value. For example, on Sheet1 if you always wanted a ranged name to refer to the cell in column A with the value "a" in it, you could use this...


=INDEX(Sheet1!$A:$A,MATCH("a",Sheet1!$A:$A,0))

Does this help?

TheAntiGates
12-18-2013, 07:24 PM
Thanks for all your hard work Zack and for your good answers when you give them. Nonetheless, if anyone else understands the original post and can directly answer, this is still unsolved. I'll continue to monitor the thread so if you find this 20 years later I'll still see and appreciate a meaningful answer. I really went to some trouble to write a seemingly unambiguous question and even after being shot down, proposed an admittedly awkward solution. If someone wants to specifically say, "yes, and here's how" or improve on the awkward workaround solution, woot woot.

Here is the question, with $ added for further clarity:

Assume cell $B$2 is named MyRange ( that is, in code, range("myrange") ) and its cell content is "foo."
I go Selection.sort.
Now MyRange is still $B$2, but instead I want MyRange to move to wherever "foo" got sorted to.
If B9 is now where "foo" is, I want MyRange to now refer to B9, not B2

Is this possible? Can MyRange be dynamic in this regard?

Zack Barresse
12-18-2013, 08:41 PM
=INDEX(Sheet1!$B:$B,MATCH("foo",Sheet1!$B:$B,0))

mikerickson
12-18-2013, 10:35 PM
If the value in the named cell is unique (within the range being sorted), you could use something like this.


Dim NameValue As Variant

NameValue = Range("MyNamedRange").Value

With Range("A2:D10")
.Sort key1:=.Cells(1, 1)

.Find(NameValue).Name = "MyNamedRange"
End With


It's similar to Zac's name definition, in that it depends on unique cell contents, but its dynamic in that the user can change the value of the named cell between sorts and still have things work.
If the value of the named cell is both unique and unchanging, defining the name by searching for that value is the way to go.

TheAntiGates
12-19-2013, 09:52 AM
Yes Mike, that's what my code does - it invents an [at least presumably] unique string as cell content after saving off the true content, so that it can be "dot found" so to speak. It sounds like you're agreeing that my code solution is the way, though you have a nice compression of

c=.find()
c.Name = "whatever"
to
.find().name="whatever"

which I never would have thought of since I'm so used to checking c against Nothing first; but that wouldn't need to be done here. Nice.

In addition you demonstrate using With and a specific sort and search range, as opposed to strictly operating on Selection, and the implication is also understood and appreciated.

I hoped that some option or setting or redefinition of the Range or something would get this done rather than the IMO "atrocity" of requiring this code. Oh well.

Finally, upon review of what I've ended up with here, it seems that the naming of a range is useless anyway (if I'm forced to go with the .Find approach)! Oh well, I tried do it the easy way, with a range.

I didn't want to muddy the airwaves in explaining why I want to do this but people seem to want to know so if anyone is still reading, here's one very direct, extreeeemely common need for this capability. It is to preserve selection upon sort. You know, when you sort, the "relative" selection does not change, though you might want it to, as it "tracks" what was selected prior to the sort.

For example, say you have this data.
Al 1
Bob 9
Cal 5
Dee 7You want to see a descending value sort, and see if Dee is near the top of the list after sorting. So you select Dee, and run a Sort. But you don't want to have to do a search for Dee to find her after the sort. Even worse, what if there are 179 other Dee entries in the list and, even though there's also a last name column, now you'd have to search for the last name which is Keihanaikukauakahihuliheekahaunaele. Ideally, assigning a temporary Range name to the selected cell and being able to .GoTo that range after sort would be great. From the responses here, though, it seems that this "clever" code approach is necessary, and that a named range wouldn't seem to be useful at all.

BTW another out-of-the-box solution would be to add a sequentially numbered helper column to the sort area, but again it feels like bombing an anthill. Another way might be to find an unused cell property like ID or even comment, and then search for THAT, but again this seems like a lot of work (and what if either of those properties are in use, e.g.). Too bad that there seems to be no option or setting or redefinition of a Range or something so that you could just GoTo after the sort.

Thanks again for both of your time in responding.

mikerickson
12-19-2013, 01:18 PM
I was hoping that your data would be unique. You're adding a dummy value to the cell isn't needed unless you are sorting non-unique values.
Alternatively, if the column that contains MyNamedRange is non-unique, you might use a different column that is unique to track where the sort moves it.

(If there is no unique column in the sorted range, does it really matter which value of "foo" MyNamedRange points to?)

SamT
12-19-2013, 02:35 PM
Assume cell B2 is named MyRange ( that is, in code, range("myrange") ) and its cell content is "foo"
I go Selection.sort
Now MyRange is still B2, but instead I want MyRange to move to wherever "foo" got sorted to.
If B9 is now where "foo" is, I want MyRange to now refer to B9, not B2
Your question is very hard to answer because your language is not critically specific enough. eg. B2 is NOT named "MyRange." Specifically, the Named Formula, (AKA: Named Range,) "MyRange" actually refers to the range $B$2. (The Named formula is indeed "=$B$2")

With a critical rewrite, your question becomes:

Assume the Worksheet Name "MyRange" refers to Cell("$B$2") and it contains the value "MyValue." After a Data Sort, MyRange stills refers to Cell("$B$2"), but I need to have it refer to the Cell that now contains "MyValue."



Attitudes:

Thanks for looking at this, Zack. Can anyone else comment on what I've done in the two posts above?


Thanks for all your hard work Zack and for your good answers when you give them. Nonetheless, if anyone else understands the original post and can directly answer, this is still unsolved. I'll continue to monitor the thread so if you find this 20 years later I'll still see and appreciate a meaningful answer. I really went to some trouble to write a seemingly unambiguous question and even after being shot down,

I find your attitude is Foo1.2 (https://en.wikipedia.org/wiki/FUBAR#FUBAR) and am closing this thread.



After reading all your posts: VBA is what it is. Deal with it.

Zack Barresse
12-19-2013, 04:17 PM
Using a helper column for a unique (foreign) key would be the way to go. You need uniques. Even so, I'd still recommend going the standard named range route and not messing with VBA. You can do this with a simple INDEX/MATCH, no VBA needed. Even if you do want to use VBA, you'd still need something to uniquely identify that cell, which can also be done with a helper column, or perhaps specific cell formatting/styles, something.

EDIT: After speaking with Sam we opened the thread. I've taken no offense and feel confident we can all get along. :)

TheAntiGates
03-23-2016, 03:58 PM
Assume cell B2 is named MyRange ( that is, in code, range("myrange") ) and its cell content is "foo"
I go Selection.sort
Now MyRange is still B2, but instead I want MyRange to move to wherever "foo" got sorted to.
If B9 is now where "foo" is, I want MyRange to now refer to B9, not B2

Is this possible? Can MyRange be dynamic in this regard?AMOQ. Leverages the nifty xlMoveAndSize quality of objects. Shown below is for a single cell range but can be modified for multiple cell ranges.
B1=Al
B2=John
B3=Carl
B2 is MyRange
Below will associate MyRange with John even though he was sorted to a new location.Sub foo()
Dim myShape As Shape
Set myShape = ActiveSheet.Shapes.AddShape(1, Range("myRange").Left + 1, Range("myRange").Top + 1, 0, 0)
myShape.Placement = xlMoveAndSize
Range("B:B").Sort Key1:=Range("B1"), Header:=xlNo
Names.Add "myRange", myShape.TopLeftCell
myShape.Delete
End Sub

TheAntiGates
03-23-2016, 04:07 PM
What's great is that there can be numerous other Johns (not in MyRange) which are disregarded by this code. Thus only the desired John is "chosen."