Macro to sort things my way

Hozz

Board Regular
Joined
Feb 10, 2005
Messages
140
Hi guys,

I need to macro to sort some columns in a certain way.

I have 6 columns of data A-F. In Column C, I have a validation list with these options in it (Black,Blue,Gold,Green,Red,White,Colourless,Land).

I need a macro that will sort the 6 columns in that order when I press a button.

Any ideas?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
you could create another column is in direct relation with your current column, ie. black=1 blue=2 gold=3 etc. so that you could create a button to sort by this new column.

are you making a library for MTG cards? i remember that game, was good fun.
 

Wolfshead

Board Regular
Joined
Oct 18, 2006
Messages
100
Hi
I think you could do it by defining a custom list (tools/options/custom lists). Then when you sort your list select the options button and pick out your custom list .
Chris
 

Hozz

Board Regular
Joined
Feb 10, 2005
Messages
140
Well spotted Jamie :biggrin:

Wolfshead, yes that seems like it would work, I have created my list and I can do a manual sort using that list.

So how do I do the macro? sorry...my macro skills are pretty pathetic :confused:
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
hey hozz.

this is the customsort routine i use at work; i think it would work for you. please note that the inputbox prompts assumes you have a column "dedicated" to your custom sort order (your list of MTG cards -- i used to love that game!). if that isn't the case, you'll want to be sure that you only select the list, not the entire column.

otherwise, i think it is ready to go.
cheers. ben.

Code:
Sub CustomSort()
'   Ben Marston
'   October 2006

    Dim rngList As Range, rngSort As Range
    
'   Get range which defines sort order
    On Error Resume Next
    Set rngList = Application.InputBox( _
                    Prompt:="Select column range to set sort order:" & _
                        Chr(13) & "(shorter of two column ranges)", _
                    Title:="Get Sort Order", _
                    Default:=Selection.Address, _
                    Type:=8)
    
    If rngList Is Nothing Then
        MsgBox "Error!  No list range selected!  Ending program..."
        Err.Clear
        GoTo EH:
    End If
    
'   Create custom list from sort order range
    Application.AddCustomList ListArray:=rngList

'   Get range to sort in custom sort order
    Set rngSort = Application.InputBox( _
                    Prompt:="Select column range to sort:", _
                    Title:="Get Sort Range", _
                    Type:=8)
        
    If rngSort Is Nothing Then
        MsgBox "Error!  No sort range selected!  Ending program..."
        Err.Clear
        GoTo EH:
    End If
    
    On Error GoTo EH:
    
'   Sort range on custom sort order
    rngSort.Sort _
            Key1:=rngSort.Cells(1, 1), _
            Order1:=xlAscending, _
            OrderCustom:=Application.CustomListCount + 1, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
     
EH:
    Application.ScreenUpdating = True
    
'   Alert user of error
    With Err
        If Err.Number <> 0 Then
            MsgBox "Error!" & Chr(13) & _
                "Description: " & .Description & Chr(13) & _
                "Number: " & .Number, _
                vbCritical + vbOKOnly, "Error Message"
        End If
    End With
    
'   Reset object variables
    Set rngList = Nothing
    Set rngSort = Nothing
    
End Sub
 

Forum statistics

Threads
1,136,655
Messages
5,677,015
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top