假如你的数据内容在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
双击把以下代码复制进去,点下按钮,sheet2的A列就会显示sheet1中各单元格出现过的内容,B列就会显示对应出现的次数,就算满表6万行都是数据
