排序并忽略前导引号

Question

此VBA代码通过双击列标题按任何列对我的黑胶唱片收藏目录进行排序。

对于我的古典黑胶唱片，将近一半的歌曲标题都是引号，所以当该列排序时，它按字母顺序排序标题首先有引号，然后标题没有引号。

是否有一种方法可以添加一行代码，以便在排序时忽略前置引号，以便"ac"在ab之后等等?

我现在的解决方法是使用一个隐藏的帮助列来删除引号，但我希望有一个更干净的解决方案。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim KeyRange    As Range
Dim ColumnCount As Integer

'Clear previous sorts
ActiveSheet.Sort.SortFields.Clear

'Clear contents of hidden helper column
Columns("K").ClearContents

'Copy and Paste songname column to helper column
Range("F:F").Copy Range("K:K")

'Strip quotes from helper column
Application.ScreenUpdating = FALSE
ActiveSheet.Columns("K").Replace What:="""", Replacement:="", LookAt:=xlPart, MatchCase:=False
Application.ScreenUpdating = TRUE

'Set range of header columns that will sort on double-click
ColumnCount = Range("A1:J1").Columns.Count
Cancel = FALSE
If Target.Row = 1 And Target.Column <= ColumnCount Then
Cancel = TRUE

'Get cell address of double-clicked header cell
Set SortColumn = Range(Target.Address)

'Set fill color of currently sorted column header
Rows(1).Interior.Color = xlNone
SortColumn.Interior.ColorIndex = 15

With ActiveSheet
'Sort by hidden column if songname column is double-clicked
If SortColumn = Range("F1") Then
Range("K1").Select
Else
SortColumn.Select
End If
'Sort by selected column followed by album, disc, then track
.Sort.SortFields.Add Key:=Selection, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=Range("E1"), _
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
'Set flexible sort range to all data before reaching entirely empty row or column
.SetRange Range("A1").CurrentRegion
.Header = xlYes
.MatchCase = FALSE
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End If
End Sub

Answer 1

Excel在排序数据时一直存在这个问题。这是因为它使用ASCII字符代码来确定排序顺序(更多详细信息请访问:https://exceljet.net/excel-functions/excel-char-function)。特殊字符和标点符号的ASCII值比字母低，因此它们在顶部排序。这是故意的。

没有真正的方法来"忽略";排序时使用引号，但解决这个问题的一种方法是从要排序的单元格中删除所有引号。您可以尝试在SET语句之后添加以下代码:

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

警告! !实际上这将修改所有你的歌名(它将从这一列去掉双引号),所以请备份你的文件在你试试这个。

Answer 2

一种方法是添加一个包含F数据的虚拟列K，清除"，排序，擦除列K…

我修改了相应的代码

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首先在副本上运行，保护原始Excel文件。

注意:我已经使用KeyRange来工作，但可能会有一些set SrcRng = Range("A1:J281")是需要的