Adjust the range references in the first and last lines as appropriate for
your list.

Sub Tanya()
Range("A2").Select

sResponse = InputBox("Enter 'A' for Ascending Order" & Chr(13) & _
"Enter 'D' for Descending Order", "Choose Sort Order...")

If sResponse = "" Or (UCase(sResponse) < "A" And UCase(sResponse) < "D")
Then _
MsgBox "Data not sorted. Sort canceled by user": Exit Sub

If UCase(sResponse) = "A" Then ord = xlAscending Else ord = xlDescending

ActiveCell.Sort Key1:=Range("A3"), Order1:=ord, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
--
Jay


"Tanya" wrote:

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