| Home |
| Search |
| Today's Posts |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a list, then sort
Thank you
"Bob Phillips" wrote: Before the sort. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tanya" wrote in message ... Thank you Bob, please forgive me, I am only beginning to learn VBA. I understand where to alter order1 but have no idea how to place the rest of your code. Regards Tanya Sub SortByName() ' ' Sort by Mechanic's Name Macro ' Macro recorded 3/03/2007 by Tanya ' ' Range("B10").Select ActiveCell.Sort Key1:=Range("B11"), Order1:=iSortOrder, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub "Bob Phillips" wrote: iSortOrder = xlAscending sSortOrder = Inputbox("Sort ascending(A) or descending(D)?",default:="A") If UCase(sSortOrder) = "D" Then iSortOrder = xlDescending End If and change the Order1 property from xlAscending to iSortOrder -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tanya" wrote in message ... Jay is it at all possible to add a line to this code which will provide a msg box which allows the user to decide whether to sort by assending or descending? "Jay" wrote: Yes, makes sense. The sort method automatically senses the size of the list; here's how you can take advantage of that fact. The sort statement produced by the macro recorder will look something like: Range("A2:C25").Sort Key1:=Range("A3"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Replace "Range("A2:C25")" with "ActiveCell", so it'll look like "ActiveCell.Sort Key1...etc. Then, add a line before that sort statement: Range("A2").Select 'Or whatever the uppermost cell in the list is. Then run it! -- Jay "Tanya" wrote: Thank you Jay for replying so promptly. I have already tried this. My problem is that the table changes as new members are added. Therefore the range is not constant... I think I need a way for the macro to identify the headings and last entry in the table. At least that is where my thoughts are heading. Does that make any sense? Regards Tanya "Jay" wrote: Hi Tanya - Try the macro recorder for this process. It will produce VB macro code that you can easily modify. First, select any cell in the list. then: Step 1: Tools | Macro | Record New Macro (a StopRecording menu bar will appear - disregard it for now) Step 2: manually apply the sort (Data | Sort | etc...) Step 3: click "Stop Recording" (on the StopRecording menu bar that appeared in Step 1. Step 4: Open the VB editor (Alt-F11) to find the sorting statement in the Macro produced by Steps 1-3. Modify as necessary. -- Jay "Tanya" wrote: Hi Could someone help me please? I need to be able to select a list in sheet and then sort by particular heading. I am new to macros. Any guidance would be appreciated. Thanks in advance |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I want sort a list but the sort is unlit on the home tab how do I | Excel Worksheet Functions | |||
| DATA VALIDATION LIST- CAN I SELECT FROM LIST WITHOUT SCROLLING | Excel Worksheet Functions | |||
| how to randomly select a name in a list and then the select the ne | Excel Worksheet Functions | |||
| Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
| Select and then sort a list | Excel Programming | |||
Threaded Mode
