Sort moves data to bottom of Sort Range???

[Brian F]

Excel 2010, windows7; After performing the sort shown below, why does the data end up at the BOTTOM of the sort range rather than at the top where it started and where it belongs? The first cell was in the upper left cell to begin with and after the sort it ends up in the bottom left cell. Whether I tell it to do the sort in ascending or descending order, the data end of at the bottom left of the worksheet. The data is sorting on text strings (not numbers). This seems to happen on occasion (in this and other worksheets) with different sorts described in the same way. VERY frustrating.

Sub Sort_Projects_Inspection_Times()
'
' Sort_Projects_Inspection_Times Macro

Range("J4:AJ1406").Select
ActiveWorkbook.Worksheets("Inspection Time").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Inspection Time").Sort.SortFields.Add Key:=Range( _
"J5:J1406"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Inspection Time").Sort.SortFields.Add Key:=Range( _
"L5:L1406"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Inspection Time").Sort
.SetRange Range("J4:AJ1406")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

 

[shg]

Sub Sort_Projects_Inspection_Times()
    Dim wks         As Worksheet
 
    Set wks = Worksheets("Inspection Time")
 
    With wks.Sort
        .SortFields.Clear
        .SortFields.Add Key:=wks.Range("J4"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending
        .SortFields.Add Key:=wks.Range("L4"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending
        .SetRange wks.Range("J4:AJ1406")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

What data is in columns J and L at the top of the sort?

 

[Brian F]

In general column J is a client name (e.g Fox Construction) and L contains a Project Code (e.g FOXnc12Wilow). The bulk the table is numerical values.

After the sort at the top of the range is what use to be at the bottom of the sort range, empty cells. I haven't tried your solution. Will do so now.

Thank you for your help!

 

[Brian F]

Nope, it still move everything to the bottom of the sort range.

 

[Brian F]

OOOOh, It's putting all of the empty cells first, duh. I need to put something in that counts the number of cells I want to sort. Like count the cells in column J that contain a value.

Sorry, my bad. Thank you again for you help.

 

[shg]

You're welcome, good luck.