none
Excel Range Sort Option MatchCase Seems To Have No Effect RRS feed

  • Question

  • Hello:

    I have a list of names to sort.  It doesn't seem to matter if I choose MatchCase = True or False, they always sort case insensitive.  So, if I sort this list:

    able
    baker
    charlie
    Able
    Baker
    Charlie

    They always come out as:

    able
    Able
    baker
    Baker
    charlie
    Charlie

    If they were case sensitive (ASCII sort method), I would expect:

    able
    baker
    charlie
    Able
    Baker
    Charlie

    Am I missing something?  Here's my sort in which I flip the true and false values of the MatchCase option, and it doesn't seem to matter:

    Public Sub SortFromVBAMethod3()
    ' **************************************************************************
    ' Mixed Numeric and Text Formats in Same Column
    ' Option Compare Text = TRUE
    ' Sort DataOption = xlSortNormal
    ' Sort MatchCase = TRUE
    ' VLOOKUP TRUE Option Used
    ' Same Sequence Achieved As Using Standard Excel Ribbon Sort Button
    ' **************************************************************************
    Dim wkbSortTestWorkbook As Workbook
    Dim wksResultsMatrix As Worksheet
    Dim lngLastRowOfDataToSort As Long
    Dim rngRangeToSort As Range
    
    strStartingColumnLetter = "R"
    lngStartingColumnNumber = ConvertColumnLetterToNumber(strStartingColumnLetter)
    
    Set wkbSortTestWorkbook = ThisWorkbook
    Set wksResultsMatrix = wkbSortTestWorkbook.Sheets("ResultsMatrix")
    
    lngLastRowOfDataToSort = wksResultsMatrix.Cells(Rows.Count, lngStartingColumnNumber).End(xlUp).Row
    Set rngRangeToSort = Range(wksResultsMatrix.Cells(2, lngStartingColumnNumber), wksResultsMatrix.Cells(lngLastRowOfDataToSort, lngStartingColumnNumber + 1))
    
    With wksResultsMatrix.Sort
        .SortFields.Clear
        .SortFields.Add Key:= _
            Columns(lngStartingColumnNumber), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        .SetRange rngRangeToSort
        .Header = xlNo
        .MatchCase = True
        .Orientation = xlTopToBottom
        .SortMethod = xlStroke
        .Apply
    End With
    
    wksResultsMatrix.Select
    wksResultsMatrix.Range("A1").Select
    End Sub
    

    Any ideas?

    Thanks,

    Rich Locus


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Sunday, November 16, 2014 10:09 PM

All replies

  • The result you got is what the meaning of "case-sensitive sort" is.

    For example, your list will be like this:

    able
    baker
    charlie
    able
    baker
    charlie

    So you will get the following result:

    able
    A(a)ble
    baker
    B(n)aker
    charlie
    C(c)harlie


    You have Failed this City:)

    Monday, November 17, 2014 7:31 AM