Sorting a section of a column of data

Question

[Moderation note: PII removed]

In the attached example PDF there is a list of businesses and their employees in column A and the number of orders placed by those employees in column B.

I have this information in an excel spreadsheet. Using a VBA macro I need to be able to sort the employees for Dave's Merchants alphabetically in column A then sort the amount of orders in descending order in column B.

I also need to be able to do this without referencing a specific cell because each time I add an employee to the companies above Dave's Merchants then the position in the list of this company will change.

Could anyone help me with this please?

---

Move from Windows for home | Windows 10 | Files, folders, and storage

Answer 1

Hi, Colin Cutts

Please note that our forum is a public platform, and we will modify your question to hide your personal information in the description. Kindly ensure that you hide any personal or organizational information the next time you post an error or other details to protect personal data. 

From your Example, the data is grouped by company header rows, then employee/order rows underneath, so the cleanest VBA approach is to find “Dave’s Merchants”, find the next company header below it, and sort only that section.

Excel VBA’s Range.Sort supports more than one sort key and lets you set whether the first row is a header. It is recommended that you build the range dynamically with a last-row / next-header check instead of hard-coding cell addresses.

Try something like this:

Sub SortDaves()
    Dim ws As Worksheet
    Dim hdr As Range
    Dim startRow As Long, endRow As Long
   
    Set ws = ActiveSheet
    Set hdr = ws.Columns("A").Find("Dave's Merchants", LookAt:=xlWhole)
    If hdr Is Nothing Then Exit Sub
   
    startRow = hdr.Row + 1
    endRow = startRow
   
    Do While ws.Cells(endRow, "B").Value <> "Orders" And ws.Cells(endRow, "A").Value <> ""
        endRow = endRow + 1
    Loop
    endRow = endRow - 1
   
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("A" & startRow & ":A" & endRow), Order:=xlAscending
        .SortFields.Add Key:=ws.Range("B" & startRow & ":B" & endRow), Order:=xlDescending
        .SetRange ws.Range("A" & startRow & ":B" & endRow)
        .Header = xlNo
        .Apply
    End With
End Sub

If you actually want highest orders first as the main sort, just swap the two .SortFields.Add lines.

Thank you for your patience in reading, I hope this information has been helpful to you.

Since this is a public forum, we will hide your personal identifiable information, and I have sent that image of yours containing your personal identifiable information in a private message so we can work together to resolve the issue. Also, please check your private message in case you want to share another screenshot to avoid leaking your PII by click on the Private Message button as show below.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.