Ignore blanks when sorting a list

**JamesT1** · 05-22-2010, 03:51 AM

Hi Folk

This is a macro I recorded to sort a list into alphabetical order,,,,,,,,, how can I stop it putting the blank cells at the top of the list,,

Sub Sort_Names()

    ActiveWindow.SmallScroll Down:=-9
    Range("A3:C600").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-54
    Range("E3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Columns("E:G").Select
    Range("E2").Activate
    Columns("E:G").EntireColumn.AutoFit
    Range("E3:G600").Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("G3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    
End Sub

thanks

**snb** · 05-22-2010, 06:14 AM

What about

Order1:=xlDescending

**Marcol** · 05-22-2010, 09:29 AM

Try this

Option Explicit

Sub Sort_Names()
    Dim LastRow As Long
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    With Range("A3:C" & LastRow)
        .AutoFilter Field:=3, Criteria1:="<>"
        .Copy Range("E3")
        .AutoFilter
    End With
    
    LastRow = Range("E" & Rows.Count).End(xlUp).Row
    Range("E3:G" & LastRow).Sort Key1:=Range("G1"), Order1:=xlAscending, _
                                Header:=xlGuess, OrderCustom:=1, _
                                MatchCase:=False, Orientation:=xlTopToBottom, _
                                DataOption1:=xlSortNormal
End Sub

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.