假如你的資料內容在sheet1表中,那就在sheet1表中建一個按鈕(commandbutton),雙擊把以下程式碼複製進去,點下按鈕,sheet2的A列就會顯示sheet1中各單元格出現過的內容,B列就會顯示對應出現的次數,就算滿表6萬行都是資料,1分鐘內出結果.
Private Sub CommandButton1_Click()
On Error Resume Next
Dim dic As Object
Dim Rng As Range
Set dic = CreateObject("scripting.dictionary")
For Each Rng In ActiveSheet.UsedRange
If Rng "" Then
dic(Rng.Value) = dic(Rng.Value) + 1
End If
Next
With ThisWorkbook.Worksheets("sheet2")
.Columns("a:b").ClearContents
.Range("a1").Resize(dic.Count, 1) = Application.Transpose(dic.keys)
.Range("b1").Resize(dic.Count, 1) = Application.Transpose(dic.items)
End With
End Sub
假如你的資料內容在sheet1表中,那就在sheet1表中建一個按鈕(commandbutton),雙擊把以下程式碼複製進去,點下按鈕,sheet2的A列就會顯示sheet1中各單元格出現過的內容,B列就會顯示對應出現的次數,就算滿表6萬行都是資料,1分鐘內出結果.
Private Sub CommandButton1_Click()
On Error Resume Next
Dim dic As Object
Dim Rng As Range
Set dic = CreateObject("scripting.dictionary")
For Each Rng In ActiveSheet.UsedRange
If Rng "" Then
dic(Rng.Value) = dic(Rng.Value) + 1
End If
Next
With ThisWorkbook.Worksheets("sheet2")
.Columns("a:b").ClearContents
.Range("a1").Resize(dic.Count, 1) = Application.Transpose(dic.keys)
.Range("b1").Resize(dic.Count, 1) = Application.Transpose(dic.items)
End With
End Sub