I need some type of auto sort thing in excel.

I have 10 entries, and i need excel to automatically sort them from greatest to least, including their respective labels (in adjacent cells). i know how to sort stuff manually, but i don't want to have to sort this list every time something changes.

what you got tdub?

3/6/2008 7:40:47 PM

how does the data that is supposed to be sorted get updated in the cells?

3/6/2008 7:47:20 PM

it references other cells. the cells that are being sorted calculate a percentage based of 10 other cells

3/6/2008 7:49:59 PM

just set up a macro to do it and refresh it

3/6/2008 8:52:14 PM

just setup a macro to sort the list in the worksheet calculate event

in excel: press alt+F11, this will open up the Microsoft Visual Basic window (it may have to be installed first)
in the tree view on the left you should see VBAProject (your workbook file name) double click on the sheet that you want this functionality added to.
in the drop down on the top of the screen that says "(General)" select "Worksheet"
in the drop down next to that select "Calculate"
in the whitespace between

Quote :

"Private Sub Worksheet_Calculate() End Sub"

put the following line:

Range("A1:E28").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Private Sub Worksheet_Calculate()
Range("A1:E28").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

3/6/2008 9:19:17 PM

thanks.

i tried that code, but it order it, greatest, least, least, least, least, next to least to next to greatest

3/7/2008 5:53:09 AM

manually do it once while using the macro recorder, then have the recorded macro do it from then on

3/7/2008 7:45:59 AM

Sounds like a bubble sort to me. Google "vba bubble sort" and see what turns up.

3/7/2008 9:43:05 AM

we're impressed with your CSC 201 knowledge. really.

but seriously, wtf does that have to do with anything? Excel/VBA already has a sufficient sorting function built in. No need to reinvent the wheel here.

3/7/2008 9:46:32 AM

^

3/7/2008 9:50:11 AM

Wow. jesus fucking christ. Sue me for offering a suggestion.

And I didn't learn it in CSC201.

3/7/2008 11:33:58 AM

regardless, the question isn't what method to use to do the actual sorting, it's how to make sure the list sorts itself automatically. Using any VBA sorting method other than "Order1:=xlAscending" is adding needless complexity to the task

3/7/2008 1:18:49 PM

still don't have a working method...

3/8/2008 8:11:24 AM

is the length of the range to sort changing? this could cause the behavior you are seeing if you are adding lines to the range to sort, the code I posted won't change to include new lines

3/8/2008 8:54:20 AM

use the function from HaLo, except change the "Calculate" to "Change". That means that every time something in the worksheet changes (like a new value is added), the function is called. This can be dangerous if you have a giant worksheet with lots of formulas and stuff because literally every single time you change a cell the sort will be called, but if you're dealing with a reasonable amount of data it will be fine.


Private Sub Worksheet_Change()
Range("A1:E28").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

3/8/2008 9:10:59 AM

if you aren't running MS Office 2003 or later, you will need to download a patch to make that VBA sort code work.

3/8/2008 4:56:39 PM

goodlord. ^^ that's the same as posted before

is there not just some built in function to do this?

[Edited on March 9, 2008 at 10:06 AM. Reason : .]

3/9/2008 10:05:11 AM

It depends what you mean by "built-in", i guess. VBA is "built-in", and VBA has a function to do it which we're showing you.
But no, there is not a feature where you can just select "keep list in order" or anything. It is pretty easy to either use Data > Sort or Data > AutoFilter to sort it manually, or just use the VBA function.

3/9/2008 10:08:19 AM

aight. well the vba didn't work right. i was thinking that there was some way to use the max function and maybe a count function. something like setting up something in individual cells (use "max" and "min" and then I was hoping there was some way to do a max - 1 type thing).

3/9/2008 10:14:00 AM

what do you mean by "the VBA didn't work"? can you post a screenshot of the VBA window so that we can be sure you have the code in the right place?

3/9/2008 12:04:37 PM

yeah, i tried the code that I pasted earlier and it worked perfectly, in Office 2000 with no patches or add-ons or anything. I kept adding numbers to the list, and as soon as I would add a number the whole list would re-sort itself. If I replaced one number with another, it would also resort itself.

3/9/2008 12:41:03 PM

i copied and pasted the code, I'll get a screenshot in a sec. The only thing I changed was ascending to descending, which almost has it working. the only problem now is that it won't sort the current value at the top of the column, but it sorts everything below it (the top value never gets sorted).



[Edited on March 9, 2008 at 1:58 PM. Reason : pic]

[Edited on March 9, 2008 at 1:59 PM. Reason : oops, that screenshot has E13 for key1 - I was just playing with it to see if i could get it to work]

3/9/2008 1:55:30 PM

PM sent

3/9/2008 7:33:33 PM

delete the following "Header:=xlGuess,"

this will stop Excel from guessing whether the first row is a header row (unsorted), its a hold over from the recorded macro code. that should work.

3/11/2008 9:04:47 PM

appreciate it everyone. ^that got it working

3/12/2008 5:25:12 AM