EXCEL中使用VBA转置数据（竖排转横排）

竖排数据包含部门和员工名称，要转成横排显示

Sub 重排数据()
    Dim ws As Worksheet
    Dim lastRow As Long, destCol As Long
    Dim deptDict As Object
    Dim deptName As Variant
    Dim empList As Collection
    Dim empName As Variant
    Dim rowCounter As Long
    
    '创建字典对象用于存储每个部门对应的员工姓名列表
    Set deptDict = CreateObject("Scripting.Dictionary")
    
    '设置要操作的工作表
    Set ws = ThisWorkbook.Sheets("Sheet1") '将"Sheet1"替换为你的工作表名称
    
    '获取最后一行的行号
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    '遍历数据，将员工姓名按部门名称分组存储在字典中
    For i = 2 To lastRow
        deptName = ws.Cells(i, "A").Value
        empName = ws.Cells(i, "B").Value
        
        If Not deptDict.exists(deptName) Then
            deptDict.Add deptName, New Collection
        End If
        
        deptDict(deptName).Add empName
    Next i
    
    '将结果输出到新的区域,将4替换为你要显示的列号，比如E列开始显示就写5
    destCol = 4
    For Each deptName In deptDict.keys
        '将部门名称写入目标位置
        ws.Cells(1, destCol).Value = deptName
        
        '将该部门下的员工姓名逐列写入目标位置
        Set empList = deptDict(deptName)
        rowCounter = 2
        For Each empName In empList
            ws.Cells(rowCounter, destCol).Value = empName
            rowCounter = rowCounter + 1
        Next empName
        
        '更新目标列号
        destCol = destCol + 1
    Next deptName
    
End Sub