Macro to sort selected area

[stephen.ditchfield]

Hello, would someone be able to set me in the right direction to
create a Macro to sort a seleted range, then sort when button is
pressed.
Range will always be column A to M, sort Desc on column I, starting at
A7 (rows 1 to 6 are header) the problem being that the number of rows
varies from 2 to 100 or so. Need the Macro to sort the selected area
thanks.
I can manage to do a macro for a set number of rows but having trouble
when the number of rows varies
regards
Ditchy
Ballarat, Australia

 

[Per Jessen]

Hi

Insert a CommandButton from the Control Toolbox menu on the sheet.
Rightclick the button and select Wiew code. Insert the code below exit VBA
editor and exit Design Mode.

Private Sub CommandButton1_Click()

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A7:M" & LastRow).Sort Key1:=Range("I7"), Order1:=xlDescending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Hopes this helps.

 

[Joerg Mochikun]

If you start the macro recorder *after* you have made your selection, the
resulting code will reference the area to be sorted as 'selection' and not a
fixed range address. It will be valid for any number of rows selected.
Attach this macro to a button and you are done.

 

[Dave Peterson]

If you're using xl2003 (or higher), you can apply data|filter|autofilter.
There's an option under each arrow that allows you to sort the filtered range by
that field.

But those empty rows scare me. Excel may not see them as part of your range to
be sorted.

If you're using xl2002 or below (or any version of excel, really), you can use a
technique at Debra Dalgleish's site:

http://contextures.com/xlSort02.html

 

[stephen.ditchfield]

If you start the macro recorder *after* you have made your selection, the
resulting code will reference the area to be sorted as 'selection' and not a
fixed range address. It will be valid for any number of rows selected.
Attach this macro to a button and you are done.






- Show quoted text -

Thank You everyone
your help has been appreciated, I have used Joerg's response as it was
the easiest for me to implement, thank you for the other suggestions I
will hopefully use them at a later date
regards
Ditchy