实现下图的效果![在这里插入图片描述]
代码如下:
Sub 分列并去重()
Dim iCol, iRow
iRow = Selection.Rows.Count
Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, Comma:=True
iCol = ActiveSheet.UsedRange.Columns.Count
For i = 2 To iCol
Range(Cells((i - 1) * iRow + 2, 1), Cells((i) * iRow + 1, 1)).Value = Range(Cells(2, i), Cells(iRow + 1, i)).Value
Next
Range(Cells(1, 1), Cells(iRow * iCol + 1, 1)).Select
Selection.RemoveDuplicates Columns:=1, Header:=xlNo
End Sub