Results 1 to 6 of 6

Challenged: ascending sort within custom sort

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Challenged: ascending sort within custom sort

    I've managed to put together a custom sort macro, and it works fine... to a degree. I want to take it to the next degree and am at loss on how to proceed. In short, the custom sort is ascending levels of distribution according to a two-column "mapping" system, and a starting-point reference. My macro sorts the proper levels, but within each level I want the items sorted ascending alphanumerically (i.e. standard ascending sort). My current macro just arranges by level, and top down within each level, with no regard to alphanumeric value.

    Any ideas how to accomplish this?

    Sub CustomSort()
    
    'Create custom sort list & sort "Panels" by "Fed By" column
    
    Dim cell As Range
    Dim cell2 As Range
    Dim val As String
    Dim x As Long
    Dim i As Integer
    Dim rng As Range
        
    Sheets("Project Summary").Select
    
    'Refresh sort list to cover for potential changes
    x = Range("panel_sort").Rows.Count
        If x > 1 Then
            Range("panel_sort").Offset(1, 0).Resize(x - 1, 1).ClearContents
        End If
    
    i = 0
    
    Do While Range("panel_sort").Rows.Count < Range("Panels[Fed By]").Rows.Count + 1
        i = i + 1
        val = Range("panel_sort").Offset(i - 1, 0).Resize(1, 1).Value
        For Each cell2 In Range("Panels[Fed By]")
            x = Range("panel_sort").Rows.Count
            If cell2.Value = val Then
                Range("panel_sort").Offset(x, 0).Resize(1, 1).Value = cell2.Offset(0, -2).Value
            End If
        Next cell2
    Loop
    
    'Create custom sort list
    Set rng = Range("panel_sort")
    Application.AddCustomList (rng)
    
    'Sort "Panels" with custom sort list
    ActiveWorkbook.Worksheets("Project Summary").Range("Panels").Sort Key1:=Range("Panels[Fed By]"), _
     Order1:=xlAscending, Header:=xlYes, _
     OrderCustom:=Application.CustomListCount + 1, MatchCase:=False
    
    'Delete custom sort list
    Application.DeleteCustomList Application.CustomListCount
    
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1