Sorting part numbers based on ascending order and also putting "a" part numbers at top

[mmilanesi]

Hello everyone, I have a lit of parts with part numbers in row b. Some numbers are only numeric and some part numbers start with "a-" (ex. 23443555, 3465787 ,A-10123 ,A-202454, etc.). I need to sort them by ascending order but have the parts starting with "a" on the top of the list. I can get them to sort to ascending order but the "a-" part numbers end up on the bottom of the list. Is there a way to fix this using the .sort method or will I need to acomplish this with an alternate method? Thanks for the help!

lastRow = Fabsheet.Cells(Fabsheet.Rows.Count, 2).End(xlUp).row

With Fabsheet.Sort
.SortFields.Clear
.SortFields.Add2 Key:=Fabsheet.Range("B6:B" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="A-", DataOption:=xlSortNormal
.SetRange Fabsheet.Range("A6:P" & lastRow) ' Assuming your data starts from A6 and goes to column P
.Header = xlYes ' If your data has headers
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[bebo021999]

If you want the lowest numerical value, then append the largest string to it:
for example, 1234 becomes ZZ-1234. After sorting, remove 'ZZ-' and return the original value.
Example code:

Dim cell As Range
For Each cell In Fabsheet.Range("B6:B" & lastrow)
    If IsNumeric(cell) Then cell.Value = "ZZ-" & cell.Value
Next
Fabsheet.Range("B6:B" & lastrow).Sort key1:=Range("B5")
For Each cell In Fabsheet.Range("B6:B" & lastrow)
    If Left(cell, 3) = "ZZ-" Then cell.Value = Mid(cell.Value, 4, 255)
Next

 

[mmilanesi]

If you want the lowest numerical value, then append the largest string to it:
for example, 1234 becomes ZZ-1234. After sorting, remove 'ZZ-' and return the original value.
Example code:

Dim cell As Range
For Each cell In Fabsheet.Range("B6:B" & lastrow)
    If IsNumeric(cell) Then cell.Value = "ZZ-" & cell.Value
Next
Fabsheet.Range("B6:B" & lastrow).Sort key1:=Range("B5")
For Each cell In Fabsheet.Range("B6:B" & lastrow)
    If Left(cell, 3) = "ZZ-" Then cell.Value = Mid(cell.Value, 4, 255)
Next

hi thans for the response. I actually am trying to get the a parts on top sorted in ascending order, and then the rest o the numbers below in ascending order. hope that makes sense!

 

[bebo021999]

hi thans for the response. I actually am trying to get the a parts on top sorted in ascending order, and then the rest o the numbers below in ascending order. hope that makes sense!

Yes, I suppose my code works exactly like your descibe.
ANyway, try to upload image/XL2BB or worksheet (link)to see what your actual data look like.

 

[myall_blues]

@bebo021999 not quite, because the numerics with ZZ don't sort in ascending order. In example numbers given by OP 3465787 should come before 23443555.

@mmilanesi to overcome this the code also needs to add zero padding to the left of the numeric part. Like so:

Sub test()
    Dim cell As Range
    Dim StrLen As Long, i As Long
    Const length As Long = 8
    Dim result As String
    For Each cell In Worksheets("Sheet1").Range("A2:A5")
        If IsNumeric(cell) Then
            StrLen = Len(cell.Value)
            For i = 1 To length
                If i = 1 Then
                    result = Mid(cell.Value, i, 1)
                ElseIf i <= StrLen Then
                    result = result & Mid(cell.Value, i, 1)
                Else
                    result = "0" & result
                End If
            Next i
            cell.Value = "Z-" & result
        End If
    Next
End Sub

This will give the following result when sorted.

You should consider adding zero padding to the 'A' numbers as well, because they will face the same problem. For example, A-300 will sort after A-202454.

Regards

Murray