Single Level Sort - question

JasonH001

New Member
Joined
Feb 12, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi,

I was able to find VBA code that would sort my data the way I wanted. Typically the WO#s are 5 digit numbers, which works great. Unfortunately it only works with numbers, and occasionally I have a letter suffix on some of my warehouse orders that seems to be causing problems. Anyway to make this work? I have included the code and a screen shot of the worksheet. Let me know if I missed anything?

VBA Code:
Sub SingleLevelSort()
Dim SelectR As Range
Dim sht As Worksheet
Dim LastRow As Long
Worksheets("Master").Unprotect "password"
On Error GoTo ErrorHandler
Set sht = ThisWorkbook.Sheets("Master")

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Set SelectR = ThisWorkbook.Sheets("Master").Range("A5:A150")

SelectR.TextToColumns Destination:=Range("A5"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

        Dim rng As Range, cell As Range
        Set rng = Range("A5:A150")
        For Each cell In rng
            Debug.Print Round(cell.Value, 0)
        Next cell
 
Worksheets("Master").Sort.SortFields.Clear
 
Range("A4:F150").Sort Key1:=Range("A4"), Header:=xlYes

Worksheets("Master").Protect "password"

 Exit Sub
ErrorHandler:
    MsgBox "No Data To Sort, Please add Orders"
    Worksheets("Master").Protect "password"
    Exit Sub
   
   
   
End Sub


SortExample001.JPG
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
The example above is the result I would like. I frequently remove and add orders which results in them being out of order (see picture attached)
 

Attachments

  • SortExample002.JPG
    SortExample002.JPG
    141 KB · Views: 5
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top