|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub test4AmoKat()
Dim d, i, j, Arr, K$, x
Set d = CreateObject("Scripting.Dictionary") '字典
Arr = [a1].CurrentRegion 'Arr = A1:C1963
For i = 1 To 3
For j = 2 To UBound(Arr)
If Len(Arr(j, i)) > 6 Then
K = Left(Arr(j, i), 6) '前6個字元為Key
d(K) = K & " " & Left(Mid(d(K), 8, 3) & i, i) '後面標註123為ABC欄都有
End If
Next
Next
' [H:H].Clear
' Range("H1").Resize(d.Count, 1) = Application.Transpose(d.items)
'刪除不完整資料
For Each x In d.items
If Mid(x, 8, 3) <> "123" Then d.Remove Left(x, 6)
Next
[G:G].ClearContents
Range("G1").Resize(d.Count, 1) = Application.Transpose(d.keys)
Set d = Nothing
End Sub |
|