Macro to sort selected area

  • Thread starter stephen.ditchfield
  • Start date
S

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
 
P

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.
 
J

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.
 
D

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
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top