Sort Selected Rows by Column X-Macro?

[jeffcoleky]

This seems like a simple question, but i don't have the answer.

I wish to Highlight any range of rows, then use a MACRO to sort it by Column "M" in Ascending order.

Can anyone help me with this please?

 

[Andrew Poulsom]

Have you tried recording a macro while doing it manually?

 

[jeffcoleky]

Have you tried recording a macro while doing it manually?

Yes, but it forces me to use the same range of rows each time.

 

[Andrew Poulsom]

What does the macro recorder give you? It can be edited to suit.

 

[jeffcoleky]

What does the macro recorder give you? It can be edited to suit.

Sub Sort_TAX_P()
'
' Sort_TAX_P Macro
' sort by tax value in column P
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    Rows("3492:3500").Select
    Range("E3492").Activate
    ActiveCell.Rows("1:9").EntireRow.Select
    ActiveCell.Activate
    ActiveWorkbook.Worksheets("Workbook_1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Workbook_1").Sort.SortFields.Add Key _
        :=ActiveCell.Offset(0, 11).Range("A1:A9"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Workbook_1").Sort
        .SetRange ActiveCell.Offset(0, -4).Range("A1:CL9")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

That is what I get. I would like to be able to select as many or as few rows as possible, anywhere on the spreadsheet, then sort ONLY those rows by column P. Any ideas on how to modify that?

PS. I really dont have the answer myself on how to edit this.. NO clue.

 

[Andrew Poulsom]

I don't have Excel 2007 to test, but try:

Sub Sort_TAX_P()
'
' Sort_TAX_P Macro
' sort by tax value in column P
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Selection.EntireRow.Columns(16), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Selection.EntireRow
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[jeffcoleky]

I don't have Excel 2007 to test, but try:

Sub Sort_TAX_P()
'
' Sort_TAX_P Macro
' sort by tax value in column P
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Selection.EntireRow.Columns(16), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Selection.EntireRow
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

This is Excel 2010. However, it worked great! thank you!