- Sub demo()
- Dim ar, d As Object, a, b, re(), br(), cnt
- a = Cells(Rows.Count, 8).End(3).Row
- b = Cells(1, Columns.Count).End(1).Column
- ar = Range(Cells(2, 8), Cells(a, b))
- Set d = CreateObject("scripting.dictionary")
- ReDim re(1 To UBound(ar), 1 To 2)
- For i = 1 To UBound(ar)
- If Not d.Exists(ar(i, 1)) Then
- cnt = cnt + 1
- d(ar(i, 1)) = cnt
- re(cnt, 1) = ar(i, 1)
- End If
- For j = 1 To UBound(ar, 2)
- re(d(ar(i, 1)), 2) = Replace(re(d(ar(i, 1)), 2) & ar(i, j), ar(i, 1), "")
- Next
- If Len(re(d(ar(i, 1)), 2)) > m Then m = Len(re(d(ar(i, 1)), 2))
- Next
- ReDim br(1 To d.Count, 1 To m + 1)
- For i = 1 To d.Count
- br(i, 1) = re(i, 1)
- For j = 1 To m
- br(i, j + 1) = Mid(re(i, 2), j, 1)
- Next
- Next
- Sheet2.Range("h2").Resize(UBound(br), UBound(br, 2)) = br
- End Sub
复制代码
求助:删除所有单元格中与关键字相同的字。.rar
(21.37 KB, 下载次数: 8)
|