Excel macro question

[Mr Larrington]

Long long ago I managed to set up an Excel macro which, when presented with an arbitrary selected range of cells in the current worksheet, sorted them, and only the selected cells, in alphabetical order of the leftmost column when I hit <CONTROL+Q>.  For e.g. if I've selected B42:D666 it should sort just that selection by alphabetical order of column B.  The recent bout of Windows reinstallation means that the PERSONAL.XLSB file is no longer to be found on even the oldest of backups chiz, so I have lost it.  Various VBA things found on the Intertubes seem to do what I want, but don't.  If any member of Thee Panel can assist in reproducing the said macro I will get Bethany (11) to say Nice Things about them.

TIA

[cygnet]

Somthing like this? It's about my limit, so not smart or concise

Sub MrLsSortingMacro()
 'Get Range
    Dim rng As Range
            Set rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
 'Evaluate Range String
        lrng = Len(rng.Address)
        colwidth = InStr(2, rng.Address, "$", 1)
        lenadd = InStr(1, rng.Address, ":", 1)
        colwidth2 = InStr(lenadd + 2, rng.Address, "$", 1)
 'Set Left Column Range
        lcol = Left(rng.Address, lenadd - 1) & ":" & Left(rng.Address, colwidth - 1) & Right(rng.Address, lrng - colwidth2)
 'Sort
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range(lcol) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range(rng.Address)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

[Mr Larrington]

I'll give it a try after I've had me dinner

[Jaded]

I tried it, it works.

a after dinner give had it I'll I've me try 

[Ham]

Just in passing, you do realise that the behaviour you are describing is also the behaviour you get by selecting a range and doing "Sort -> Custom Sort"

Not sure what you would get if you recorded that as a macro, but that would likely work too.

[cygnet]

Recording it gives you the same range selection every time, so I added the bit at the top.

You probably don't have to do custom sort, just a Sort > A-Z

Mine is at about the limit of my knowledge, so I'm happy if it works (even if there is an easier way)   

[Mr Larrington]

The one I had before did the sort on an already-selected range rather than asking for one to be selected after it's been invoked.  Also it gives me an message:

Code: [Select]

Run-time error '438':

Object doesn't support property or method

Clicking "Debug" highlights the line(s):

Code: [Select]

ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range(lcol) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Dunno if it makes any difference that this is Office 2013?

Bethany (11):

Cygnet is a Prince among Men; his are the ways of Truth, Light and General Awsumness

[cygnet]

Alas, I am not backward compatible

Perhaps you could achieve the same by macro recording the step after 'the Sort comment, and copying that bit to comply with 2013? It seems like the top bit works although post command rather than preselected.

You would need to look at the line ActiveWorkBook.Worksheet("worksheet name") to check how they have changed it compared to W10, I think.


But thanks for the compliment Bethany(11)

[Lightning Phil]

If you’ve already selected the range of cells, something like the following to get that range into your macro.  But simply recording the steps after you’ve selected the range (of cells) should generate the VBA for your macro.

Dim obj As Object
Set obj = Application.Selection
If TypeName(obj) <> "Range" Then
    Exit Sub
End If
Dim rngSelection As Range
Set rngSelection = obj

[Mr Larrington]

Code: [Select]

Sub MrLsSortingMacro()
'
' MrLsSortingMacro Macro
'
' Keyboard Shortcut: Ctrl+q

    Dim obj As Object
    Set obj = Application.Selection
    If TypeName(obj) <> "Range" Then
        Exit Sub
    End If

' Get Range
    Dim rng As Range
    Set rng = obj

' Evaluate Range String
    lrng = Len(rng.Address)
    colwidth = InStr(2, rng.Address, "$", 1)
    lenadd = InStr(1, rng.Address, ":", 1)
    colwidth2 = InStr(lenadd + 2, rng.Address, "$", 1)

' Set Left Column Range
    lcol = Left(rng.Address, lenadd - 1) & ":" & Left(rng.Address, colwidth - 1) & Right(rng.Address, lrng - colwidth2)

'Sort
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range(lcol) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range(rng.Address)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

appears to duplicate the functionality of the Lost Ark Macro.  Yay and, moreover, w00t and thanks to Cygnet & Lightning Phil!

Bethany (11):

Lightning Phil joins Cygnet, my Da... R Uran an' W Barguil in the Pantheon of Fkn Legends!

Edit: PERSONAL.XLSB now has its own entry in the Backup of Useful Stuffs that Get Wiped When Windows is Reinstalled.