 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / August 2007Sort macro with descending
Hi... I'm trying to put in a macro that will sort the data in column G... what I have now is: Sub SortMe() Cells.Sort Key1:=Range("G1"), Header:=xlYes End Sub But, this sorts in ascending order and I need it to be descending with the highest on top? Is there a way to get it to reverse? Thanks! :) (I know I can manually sort but this will be a macro that is set to auto-run when the file is open so that's why I want it incorporated into the macro) try recording a macro while you are doing a manual sort to see what happened. Modify to suit  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Hi... I'm trying to put in a macro that will sort the data in column > G... what I have now is: [quoted text clipped - 11 lines] > auto-run when the file is open so that's why I want it incorporated > into the macro) Probably should've mentioned that I don't know much about macros... someone else wrote that one for me... I don't know how to record a macro? > try recording a macro while you are doing a manual sort to see what > happened. Modify to suit [quoted text clipped - 24 lines] > > - Show quoted text - If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Probably should've mentioned that I don't know much about macros... > someone else wrote that one for me... I don't know how to record a [quoted text clipped - 28 lines] >> >> - Show quoted text - Click on Tools | Macro | Record New Macro and a panel will pop up allowing you to specify the name of the macro you are about to record, to choose where it whould be located, and to give the macro a keyboard shortcut. When this is done you get a small toolbar mid-screen with only two icons - one to stop the recording and the other to switch between absolute and relative addressing. Your actions will be recorded, so Don was suggesting that you should go through the motions of sorting your data in descending order. At the end you should click on the Stop recording icon (NOT on the Close (X) icon). Then you can examine your code - Alt-F11 will bring up the Visual Basic Editor, and you can amend what has been recorded. Hope this helps. Pete On Aug 14, 4:15 pm, yolanda.si...@gmail.com wrote: > Probably should've mentioned that I don't know much about macros... > someone else wrote that one for me... I don't know how to record a [quoted text clipped - 30 lines] > > - Show quoted text - > Click on Tools | Macro | Record New Macro and a panel will pop up > allowing you to specify the name of the macro you are about to record, [quoted text clipped - 51 lines] > > - Show quoted text - Thanks, Pete :) You helped me once before! One other question... is there a way to get it to ignore the blank rows? I have in my formula that if the sum of them is 0, it just leaves it blank... but, it's sorting that in so it puts that on top. I'd rather not have to have the 0's in place... makes the spreadsheet look... well... crowded :) If a cell is completely blank then it will get sorted at the end of your list if it is in increasing order (which is what I normally use), so presumably it appears at the top in descening order. You might like to try returning a space " " within your formulae instead of "", as this should go to the bottom of a descending list. Hope this helps. Pete On Aug 14, 5:00 pm, yolanda.si...@gmail.com wrote: > > Click on Tools | Macro | Record New Macro and a panel will pop up > > allowing you to specify the name of the macro you are about to record, [quoted text clipped - 61 lines] > > - Show quoted text - > If a cell is completely blank then it will get sorted at the end of > your list if it is in increasing order (which is what I normally use), [quoted text clipped - 75 lines] > > - Show quoted text - I must be doing something wrong :( The table has 7 columns... one for a name, 5 for days of the week (Monday-Friday) and one for a total at the end... each day, there are 3 total points given - the highest person gets 3, the second highest gets 2, the third highest gets 1... no one else gets anything. My formula for the totals is: =IF(SUM(B2:F2)=0," ",SUM(B2:F2)) I changed it to include the extra space between the "" as you suggested. There are 19 rows in the spreadsheet, the first being the header... but there are only 14 names (so 5 rows are totally blank)... when I run the macro... which looks like this: Sub SortMe() Cells.Sort Key1:=Range("G1"), Order1:=xlDescending, Header:=xlYes End Sub When I do it... it's odd... there are 5 names on top - the ones that have no "points" so their total was nothing... then the 4 blank rows... and THEN the rest of the people who had points, sorted highest to lowest... I don't understand why it's doing that? Did I do something wrong in the formula or the macro? Thanks! :) I didn't realise you were sorting by numeric values. You can make your formula: =SUM(B2:F2) which will return zeros for those who have not scored any points. Now when you sort in descending order the names with no points will appear at the bottom of the list. To avoid the complete blanks at the bottom of the list, make sure your sort range does not include them. If you want the zeros to appear blank, you can apply conditional formatting to the cells, such that if the cell contents is zero then apply a foreground colour of white (assuming your background is also white). Hope this helps. Pete On Aug 15, 1:28 pm, yolanda.si...@gmail.com wrote: > I must be doing something wrong :( > [quoted text clipped - 28 lines] > > - Show quoted text - > I didn't realise you were sorting by numeric values. You can make your > formula: [quoted text clipped - 51 lines] > > - Show quoted text - Oooh... that is helpful! I didn't know you could format it to have a background color if it's a certain criteria? Any suggestions how I might go about doing that? That's more complicated than I've done before... I was doing good to have the "IF" statements :) On Aug 15, 10:11 am, yolanda.si...@gmail.com wrote: > > I didn't realise you were sorting by numeric values. You can make your > > formula: [quoted text clipped - 58 lines] > > - Show quoted text - OOOH!! Nevermind!! I found it!!! I made it work!! :) That's incredible! I didn't even know that was possible!!! THANK YOU!!!!!!!!!!!!!!!!!!!!!! :) I take it that you are happy with what you've ended up with, then !! <bg> Thanks for feeding back, Yolanda. Pete On Aug 15, 3:28 pm, yolanda.si...@gmail.com wrote: > > Oooh... that is helpful! I didn't know you could format it to have a > > background color if it's a certain criteria? Any suggestions how I [quoted text clipped - 9 lines] > > - Show quoted text - > I take it that you are happy with what you've ended up with, then !! > <bg> [quoted text clipped - 20 lines] > > - Show quoted text - I am, indeed, quite pleased :) One last question! Do you know if there is a way to make the macro work even if the worksheet is protected? I have it locked so that no one can change (or erase) the formula that will calculate the total points... but, doing this causes the macro to ask me if I want to "debug" it everytime I try to run it. It runs perfectly if I just unlock the spreadsheet... Any thoughts? I accidentally replied to author (your email address, I think) when I first asked this - so you may see this pop into your email account if you haven't already! :) Thanks! -Yolanda Hi Yolanda, yes, I did get the message via email as well, but I'll respond here to keep the thread going. What you can do within the macro is to unprotect the sheet first (the password is provided within the macro), then do what the macro is meant to do, and then protect the sheet again at the end of the macro. Once you are happy that everything is working, you can also protect the macro with a password to prevent people from seeing your code. However, security is not very tight within Excel, and a determined user could easily get past your passwords. If security is a major issue, then Excel is not the best platform to build an application on. Hope this helps. Pete On Aug 15, 7:27 pm, yolanda.si...@gmail.com wrote: > > I take it that you are happy with what you've ended up with, then !! > > <bg> [quoted text clipped - 38 lines] > > - Show quoted text - > Hi Yolanda, > [quoted text clipped - 61 lines] > > - Show quoted text - Unfortunately, I've been a little busy with work today so I haven't had a chance to check this until just now! When you say I can have a macro unprotect and protect the sheet again... is there something I could use for that? Would that be something to ues the macro recorder thing for? Would I just have 3 separate macros? One for unlocking, one for sorting and then one for locking it again? I know I ask a ton of questions.. unfortunately, I have very little experience with excel yet I seem to keep being the chosen one for these spreadsheets! :) I've learned so much, though! These groups have been tremendously helpful!! As for Excel not being secure... there's no one who would attempt to find the data or passwords or anything - having the macro lock/unlock the sheet will be security enough. I only want it locked so they can't mess up the formulas... :) You can place the unprotect and protect inside your original macro. Sub whatever() ActiveSheet.Unprotect Password:="justme" do your sorting stuff here ActiveSheet.Protect Password:="justme" End Sub Gord Dibben MS Excel MVP >Unfortunately, I've been a little busy with work today so I haven't >had a chance to check this until just now! [quoted text clipped - 13 lines] >enough. I only want it locked so they can't mess up the >formulas... :) Sub SortMe() Cells.Sort Key1:=Range("G1"), Order1:=xlDescending, Header:=xlYes End Sub Which you could easily have found by using the macro recorder. Gord Dibben MS Excel MVP >Hi... I'm trying to put in a macro that will sort the data in column >G... what I have now is: [quoted text clipped - 11 lines] >auto-run when the file is open so that's why I want it incorporated >into the macro)
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.