如何用VBA正确排序E列带后缀编号的表格数据？

这个问题我之前也踩过坑！本质是Excel把BDB1-1、BDB1-10这类字符串当成纯文本排序，只会逐个字符比较——破折号后的第一个字符是1（不管后面跟着0还是啥），自然会排在2前面。给你两个简洁靠谱的解决办法：

方法一：临时辅助列法（最稳定通用） #

这是我平时最常用的方案，不用修改原数据，处理完自动清理辅助列，代码直接复用：

Dim LastRow As Long
With Sheets("Probeninventar")
    ' 获取E列最后一行数据行号
    LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
    
    ' 插入临时辅助列（这里用AA列，可根据实际调整），提取破折号后的数字并转为数值
    .Range("AA8:AA" & LastRow).Formula = "=VALUE(RIGHT(E8,LEN(E8)-FIND(""-"",E8)))"
    
    ' 按辅助列的数值升序排序（如果有不同前缀，可再加E列作为次要关键字）
    .Range("A8:Z" & LastRow).Sort _
        Key1:=.Range("AA8"), Order1:=xlAscending, _
        Key2:=.Range("E8"), Order2:=xlAscending, _
        Header:=xlNo ' 如果你的表头在第7行，这里设xlNo；如果第8行是表头，改xlYes
    
    ' 删除临时辅助列
    .Columns("AA").Delete
End With

为什么这个方法好用？ #

• 不管你的编号是BDB1-1还是BDB2-100，只要格式是[前缀]-[数字]，都能精准按数字大小排序
• 临时列用完就删，不会污染原工作表结构

方法二：自定义排序规则（适合固定小范围编号） #

如果你的编号范围固定（比如只有BDB1-1到BDB1-11），可以提前设置自定义排序列表，VBA里直接调用：

Dim LastRow As Long
LastRow = Sheets("Probeninventar").Cells(Sheets("Probeninventar").Rows.Count, "E").End(xlUp).Row

With Sheets("Probeninventar")
    ' 添加自定义排序列表
    Application.AddCustomList ListArray:=Array("BDB1-1", "BDB1-2", "BDB1-3", "BDB1-4", "BDB1-5", _
                                              "BDB1-6", "BDB1-7", "BDB1-8", "BDB1-9", "BDB1-10", "BDB1-11")
    
    ' 使用自定义列表排序
    .Range("A8:Z" & LastRow + 1).Sort _
        Key1:=.Range("E8"), Order1:=xlAscending, _
        OrderCustom:=Application.CustomListCount + 1, _
        Header:=xlNo
    
    ' 清理自定义列表（可选，避免影响其他操作）
    Application.DeleteCustomList Application.CustomListCount
End With

注意事项 #

• 这个方法只适合编号范围固定的场景，如果后续编号增加到BDB1-12，得手动更新数组
• 记得用完删除自定义列表，不然会留在Excel里影响其他排序操作

内容的提问来源于stack exchange，提问作者Bendaua