求Excel宏代码：合并同名行Option字段并排序、删除序号列

以下是完全满足你三个需求的VBA宏代码，直接复制到Excel的VBA编辑器中运行即可：

Sub ProcessData()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Dim nameCol As Integer, optionCol As Integer, noCol As Integer
    Dim mergedDict As Object
    
    ' 指定当前活动工作表
    Set ws = ActiveSheet
    ' 创建字典存储合并后的名称与对应选项
    Set mergedDict = CreateObject("Scripting.Dictionary")
    
    ' 遍历表头找到目标列的位置
    For i = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
        Select Case UCase(ws.Cells(1, i).Value)
            Case "[NO.]"
                noCol = i
            Case "[NAME]"
                nameCol = i
            Case "[OPTION]"
                optionCol = i
        End Select
    Next i
    
    ' 1. 删除[no.]列
    If noCol > 0 Then
        ws.Columns(noCol).Delete
        ' 删除后调整其他目标列的索引
        If noCol < nameCol Then nameCol = nameCol - 1
        If noCol < optionCol Then optionCol = optionCol - 1
    End If
    
    ' 2. 合并相同[name]对应的[option]字段
    lastRow = ws.Cells(ws.Rows.Count, nameCol).End(xlUp).Row
    For i = 2 To lastRow
        Dim currentName As String, currentOption As String
        currentName = ws.Cells(i, nameCol).Value
        currentOption = ws.Cells(i, optionCol).Value
        
        If mergedDict.Exists(currentName) Then
            mergedDict(currentName) = mergedDict(currentName) & ", " & currentOption
        Else
            mergedDict(currentName) = currentOption
        End If
    Next i
    
    ' 清空原有数据（保留表头）
    ws.Rows("2:" & lastRow).ClearContents
    
    ' 将合并后的数据写入工作表
    i = 2
    For Each key In mergedDict.Keys
        ws.Cells(i, nameCol).Value = key
        ws.Cells(i, optionCol).Value = mergedDict(key)
        i = i + 1
    Next key
    
    ' 3. 按[option]字段升序排列
    lastRow = ws.Cells(ws.Rows.Count, nameCol).End(xlUp).Row
    ws.Range(ws.Cells(1, nameCol), ws.Cells(lastRow, optionCol)).Sort _
        Key1:=ws.Cells(1, optionCol), Order1:=xlAscending, Header:=xlYes
    
    ' 释放对象
    Set mergedDict = Nothing
    Set ws = Nothing
    
    MsgBox "数据处理完成！"
End Sub

使用说明： #

1. 确认Excel表头包含[no.]、[name]、[option]（不区分大小写）
2. 运行前建议备份数据，避免意外
3. 如果提示字典功能不可用，可在VBA编辑器的「工具→引用」中勾选「Microsoft Scripting Runtime」

内容的提问来源于stack exchange，提问作者박다여