Friday, 27 June 2014

How to do Advanced Sorting the Desired Selected Range Dynamically in Excel

Dynamic Macro for Advanced Sorting the Desired Selected Range
This Macro is very Dynamic used for Advanced Sorting the Desired Selected Range by Desired Column.

Sub Adv_Sorting()

MyRange = Selection.Address
First_Row = Selection.Row
Rows_Selected = Selection.Rows.Count
Last_Row = (Selection.Rows.Count + Selection.Row) - 1

SC:
Sort_Column = InputBox("Please Enter Column by Which You Want to Sort" & vbNewLine _
& "Eg: A , B , C ...", "Please Enter Advanced Sorting Column")

'[To Exit Macro On Clicking 'Cancel' of Input box]
If Sort_Column = vbNullString Then Exit Sub 

X = UCase(Sort_Column)
Sort_Range = "$" & X & "$" & First_Row & " : " & "$" & X & "$" & Last_Row

If X = "" Or Len(X) > 1 Then

MsgBox "Please Give Column Name as : A , B, C ...", vbOKOnly, "Try Again"
GoTo SC:
End If

ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key _
        :=Range(Sort_Range), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range(MyRange)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts