Sort and ignore leading quotation marks-CodePudding

This VBA code sorts my vinyl collection catalogue by any column by double-clicking the column header.

With my classical vinyl, nearly half of the song titles are in quotes and so when that column is sorted, it alphabetically sorts the titles with quotes first, then the titles without quotes.

Is there a way to add a line of code so that it will ignore the leading quotation marks when sorting so that "ac" comes after ab and so on?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim KeyRange As Range
    Dim ColumnCount As Integer
    
    ActiveSheet.Sort.SortFields.Clear
    
    ColumnCount = Range("A1:J281").Columns.Count
    Cancel = False
    
    If Target.Row = 1 And Target.Column <= ColumnCount Then
        Cancel = True
    
        Set KeyRange = Range(Target.Address)
    
        With ActiveSheet
            .Sort.SortFields.Add Key:=KeyRange, _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Sort.SortFields.Add Key:=Range("F1"), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Sort.SortFields.Add Key:=Range("B1"), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Sort.SortFields.Add Key:=Range("C1"), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End With
    
        With ActiveSheet.Sort
            .SetRange Range("A1:J281")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
            
    End If
End Sub

CodePudding user response：

Excel has always had this issue when sorting data. This is because it uses ASCII character codes to determine sort order (more details here: https://exceljet.net/excel-functions/excel-char-function). Special characters and punctuation have lower ASCII values than alphabet letters, so they get sorted at the top. This is by design.

There's no real way to "ignore" quotation marks when sorting, but one way to get around this is to remove all the quotes from the cells you are trying to sort. You could try adding this code right after your SET statement:

ActiveSheet.KeyRange.Cells.Replace _
    What:="""", _
    Replacement:="", _
    LookAt:=xlPart, _
    MatchCase:=False

WARNING!!! This will actually modify all your song titles (it will remove the double quotes from that column), so please back up your file before you try this.

CodePudding user response：

One way is to Add a dummy Column K with F data, clear ", sort, erase column K...

I fixed the code accordingly

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim KeyRange As Range
    Dim ColumnCount As Integer
    Dim dstRng as Range               ' placeholder 2 add column "K"
    
    ActiveSheet.Sort.SortFields.Clear
    
    ColumnCount = Range("A1:J281").Columns.Count
    Cancel = False
    
    If Target.Row = 1 And Target.Column <= ColumnCount Then
        Cancel = True
    
        Set KeyRange = Range(Target.Address)
        Set dstRng = KeyRange.Resize(, 1).Offset(, KeyRange.Columns.Count - 1) 'Added column K
        KeyRange.Resize(,1).Offset(,5).copy    ' copy column F
        dstRng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
        dstRng.Replace What:="""", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

        With ActiveSheet  ' columns F,B,C >> K,B,C
            .Sort.SortFields.Add Key:=KeyRange, _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Sort.SortFields.Add Key:=Range("K1"), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Sort.SortFields.Add Key:=Range("B1"), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Sort.SortFields.Add Key:=Range("C1"), _
              SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End With
    
        With ActiveSheet.Sort
            .SetRange Range("A1:K281")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        dstRng.ClearContents

    End If
End Sub

As Always run 1st on a copy, protecting the original Excel file.

NOTES: I have used KeyRange to work on, but there might be a case that some set SrcRng = Range("A1:J281") is needed