Select a list, then sort
 

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

Select a list, then sort
 

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

Select a list, then sort
 

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

Select a list, then sort
 

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

Select a list, then sort
 

Thank you Jay sooo much.

You are a genious. I would have spent a great deal of time on this if it
wasn't for you.

Kindest Regards
Tanya

"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

Select a list, then sort
 

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

Select a list, then sort
 

By referring to the required range object, you can use .sort method.
e.g.: Below code will sort Range A1: to A100 in ascending order

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

Also, since you are new, you can record a macro in excel, for what you want
to do and then see the code in recorded macro.

Sharad

"Tanya" wrote in message
...
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

Select a list, then sort
 

I realized that your Q. is already answered by Jay.
Just in case, if you have header row, make Header:=xlYes

"Sharad" wrote in message
...
By referring to the required range object, you can use .sort method.
e.g.: Below code will sort Range A1: to A100 in ascending order

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

Also, since you are new, you can record a macro in excel, for what you
want to do and then see the code in recorded macro.

Sharad

"Tanya" wrote in message
...
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

Select a list, then sort
 

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

Select a list, then sort
 

Thank you

"Sharad" wrote:

I realized that your Q. is already answered by Jay.
Just in case, if you have header row, make Header:=xlYes

"Sharad" wrote in message
...
By referring to the required range object, you can use .sort method.
e.g.: Below code will sort Range A1: to A100 in ascending order

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

Also, since you are new, you can record a macro in excel, for what you
want to do and then see the code in recorded macro.

Sharad

"Tanya" wrote in message
...
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

Select a list, then sort
 

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

Select a list, then sort
 

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

Select a list, then sort
 

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

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