Sorting with Macros

  • Thread starter Thread starter MichaelA
  • Start date Start date
M

MichaelA

Hello all, I'm new here

To make it short: How can I apply a sort macro to only selected rows?


The details:
My sheet has multiple columns that I need to sort selected rows, in
either ascending or descending order, with a secondary sort criteria in
another column.

The problem is getting the macro to sort the right data.

Whenever I create a macro to sort by 2 criteria, its wants to include
the cell range in the macro. But this is a long sheet, where I want
to be able to select just a few continious rows at a time, and run the
sort macro on just those rows.

How do I do this?

Mike
 
I think Don's answer was to run the macro using the current selection.

I got this when I turned on the macro recorder:

Range("D9:H16").Select
Selection.Sort Key1:=Range("D9"), Order1:=xlAscending, _
Key2:=Range("F9"), Order2:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:= False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal


(I use xl2002, so you may see a couple of things that earlier versions don't
support/record.)

But it's rare when you have to select a range first. If you know how to
determine the range, you could do something like:

dim myRng as range
with activesheet
set myrng = .range("d9:H16")
myrng.sort Key1:=.Range("D:"), Order1:=xlAscending, _
Key2:=.Range("F:F"), Order2:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
end with


I think that Don's answer was pretty vague is because your question didn't have
many details.

For instance: How do you find the range to sort?
 
OP said
But this is a long sheet, where I want
to be able to select just a few continuous rows at a time, and run the
sort macro on just those rows.

so my suggestion was to sort the selection
Sub sortsel()
Selection.Sort Key1:=Selection.Range("a2"), Order1:=xlAscending,
Header:=xlGuess, _
orderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Dave Peterson said:
I think Don's answer was to run the macro using the current selection.

I got this when I turned on the macro recorder:

Range("D9:H16").Select
Selection.Sort Key1:=Range("D9"), Order1:=xlAscending, _
Key2:=Range("F9"), Order2:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:= False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal


(I use xl2002, so you may see a couple of things that earlier versions don't
support/record.)

But it's rare when you have to select a range first. If you know how to
determine the range, you could do something like:

dim myRng as range
with activesheet
set myrng = .range("d9:H16")
myrng.sort Key1:=.Range("D:"), Order1:=xlAscending, _
Key2:=.Range("F:F"), Order2:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
end with


I think that Don's answer was pretty vague is because your question didn't have
many details.

For instance: How do you find the range to sort?
Click to expand...
 
try this. Assign to a button or shape>make your selection> click

Sub sortsel()
Selection.Sort Key1:=Selection.Range("a2"), Order1:=xlAscending,
Header:=xlGuess, _
orderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
 
Guys, I really appreciate your help.

Here is the more complete story.

The sheet is a scoresheet for a weightlifting contest. (go to
www.powerlifting.ca to check it out) The events are broken up into
sessions, with a different weight class or age group in each. From a
sheet of up to 300 entries, I will work with a current session of 30 or
40. I want to be able to select that 30 or 40, and run my sort macro
on it.

When I select nothing but a top row cell, the macro recorder selects
down as far as my formulas are extended, row 1070, and applies the
macro to the whole thing. Not what I want.

One issue here is that I know Excel well, but not the VB aspects, I'm
quite new to VB. Everytime I try editing a VB Macro I get an error.

Here is the macro:
Sub sel_sort_total()
'
' sel_sort_total Macro
' Macro recorded 10/26/2003 by Mike A
'

'
Range("A10:AI1070").Sort Key1:=Range("H10"), Order1:=xlAscending,
Key2:= _
Range("G10"), Order2:=xlAscending, Key3:=Range("Y10"), Order3:=
_
xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:=False,
_
End Sub

The issue is the Range. I tried changing it to just Selection.sort, got
an error about Incorrect Syntax.

Remember my inexperience when making suggestions please.

Mike
 
how are the 30 or 40 separated - How would the macro determine what the top
and bottom of the range is. Are the delimited by blank rows, for example?

Do they all have a common value in a single column, for example?

Then, how would you specify which group you wanted sorted?
 
The selection is full rows, continious, with no blank rows. Like rows
200 thru 215 (for example) would be selected, then a macro button
clicked to sort that range. Just like if you select a range and click
the A-Z sort on the Toolbar. I just need it to sort on 2 criteria.

To explain a bit: In a powerlifting contest, each competitor gets 3
attempts at each lift, with the "who goes first" order from the
lightest lift to the heaviest. (my first sort criteria) If two are
going to do the same weight, then the one with the lowest pre-drawn
random lot number goes first. (the second criteria). After the first
lift is done by all, the second lift is entered in the next columns,
and the sheet is resorted on that column (it not always the same).

This sheet is used as the scoreboard via projector, and so that the
competitors can follow the order, so I need to be able to sort quickly.
I can do it by hand (select, Data/Sort, set the columns....) but a
macro button at the top of each column would be way faster. Just
select and click.

Mike
 
OK, Try this. Select the rows from within the range such as a5:a10 and
click. A5:h10 should sort based on col A.

Sub sortsel()
x = Selection.Range("a1").Row
y = Selection.Rows.Count + x - 1
Range("a" & x & ":" & "h" & y & "").Sort Key1:= _
Selection.Range("a2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
 
Sub Button1_click()
btnName = Application.Caller
Set rng = ActiveSheet.Buttons( _
btnName).TopLeftCell
Selection.EntireRow.Sort _
Key1:=rng, _
Order1:=xlAscending, _
Key2:=Range("A2"), _
Order2:=xlAscending, _
Header:=xlNo, _
orderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

change A2 to the column that contains the random numbers

Use buttons from the forms Toolbar rather than the control toolbox toolbar.
Assign this macro to all buttons.

Of this doesn't work, send me a sample workbook with some sample data and
say what isn't working.
 
Don, I saw your post first, and tried it out, it seems to work okay.

Tom, I'm saving yours and will try it out also, it look like it will
work too, once I get the buttons created.

Thanks for your help guys.

Mike
 
I guess you need to clarify your requirement. Don's sorts on one condition.
You said you needed to sort on two conditions. Mine was set to sort on two,
so I guess you need to ignore that.
 
Back
Top