locked
Sorting in Excel using vba RRS feed

  • Question

  • Hi,

    I have the following code :

    Range("B14:FT64").Select

    Private Sub CommandButton5_Click()
    'Sorting Names aphabatically

        With ActiveSheet
           .Unprotect Password:="1122"
            .Range("B14:FT64").Select
           
         Selection.Sort Key1:=Range("B14"), Order1:=xlAscending, _
                 Header:=xlGuess, OrderCustom:=1, _
                    MatchCase:=False, Orientation:=xlTopToBottom
           
            ActiveSheet.Protect "1122", DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, AllowSorting:=True
            .Range("C14").Select
         End With

        End Sub

    Please note:

    Cells in column B has the following formula (Example: in B14)
    =TRIM(C14&" "&D14&" "&E14&" "&F14)

    When it makes the sorting all empty cells are at the beginning. What should I change so as to have the names first and the empty cells last?

    Thursday, September 4, 2014 2:24 PM

All replies

  • Sub TestMacro()
        'This does the sorting the way you want:
        
        With ActiveSheet
            .Unprotect Password:="1122"
            .Range("B14:FT64").Sort Key1:=Range("B14"), Order1:=xlDescending, _
            Header:=xlYes, OrderCustom:=1, _
            MatchCase:=False, Orientation:=xlTopToBottom
            .Range("B14:FT64").Resize(Application.CountIf(Range("B14:B64"), "?*")).Sort _
            Key1:=Range("B14"), Order1:=xlAscending, _
            Header:=xlYes, OrderCustom:=1, _
            MatchCase:=False, Orientation:=xlTopToBottom
            
            .Protect "1122", DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, AllowSorting:=True
            .Range("C14").Select
        End With
    End Sub


    You can also change this to handle the range size dynamically.



    Thursday, September 4, 2014 4:39 PM