如何用Excel宏高效合并相同机构对应的指定列单元格？

核心功能实现代码 #

以下宏可自动识别A列中相同的机构名称，合并对应行的A列单元格，同时同步合并AC、AD、AE、AF列的对应单元格，完全避开隐藏的E-AB列：

Sub MergeSameInstitutionCells()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, startRow As Long
    Dim currentInstitution As String
    
    ' 指定目标工作表，可替换为实际表名，如"Sheet1"
    Set ws = ThisWorkbook.ActiveSheet
    ' 获取A列最后一行数据行号
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 关闭屏幕更新和警告，提升运行效率
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    startRow = 2 ' 假设表头在第1行，从第2行开始处理数据
    currentInstitution = ws.Cells(startRow, "A").Value
    
    ' 遍历A列，识别连续相同的机构
    For i = startRow + 1 To lastRow
        If ws.Cells(i, "A").Value <> currentInstitution Then
            ' 合并A列连续相同机构的单元格
            ws.Range(ws.Cells(startRow, "A"), ws.Cells(i - 1, "A")).Merge
            ' 合并对应行的AC-AF列单元格
            ws.Range(ws.Cells(startRow, "AC"), ws.Cells(i - 1, "AC")).Merge
            ws.Range(ws.Cells(startRow, "AD"), ws.Cells(i - 1, "AD")).Merge
            ws.Range(ws.Cells(startRow, "AE"), ws.Cells(i - 1, "AE")).Merge
            ws.Range(ws.Cells(startRow, "AF"), ws.Cells(i - 1, "AF")).Merge
            
            ' 更新当前机构和起始行
            currentInstitution = ws.Cells(i, "A").Value
            startRow = i
        End If
    Next i
    
    ' 处理最后一组相同机构的单元格
    ws.Range(ws.Cells(startRow, "A"), ws.Cells(lastRow, "A")).Merge
    ws.Range(ws.Cells(startRow, "AC"), ws.Cells(lastRow, "AC")).Merge
    ws.Range(ws.Cells(startRow, "AD"), ws.Cells(lastRow, "AD")).Merge
    ws.Range(ws.Cells(startRow, "AE"), ws.Cells(lastRow, "AE")).Merge
    ws.Range(ws.Cells(startRow, "AF"), ws.Cells(lastRow, "AF")).Merge
    
    ' 恢复系统设置
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    MsgBox "合并操作完成！", vbInformation
End Sub

使用注意事项 #

• 先备份数据：运行宏前建议复制一份原始数据，避免意外错误导致数据丢失
• 调整表头行：如果你的表头不在第1行，修改代码中startRow = 2的数值为实际数据起始行
• 指定工作表：若不需要激活当前工作表，可将Set ws = ThisWorkbook.ActiveSheet改为Set ws = ThisWorkbook.Worksheets("你的表名")
• 格式自定义：合并后的单元格默认居中对齐，若需要调整，可在代码末尾添加格式语句，例如：

  ' 设置A列合并单元格左对齐
  ws.Range(ws.Cells(startRow, "A"), ws.Cells(lastRow, "A")).HorizontalAlignment = xlLeft
  

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