VBA统计计算每个词的出现次数并排序

在这里插入代码片

Sub 获取排序()
    
    last = Sheet1.UsedRange.Rows.Count '获取最后一行
    arr = Sheet1.UsedRange
    Set dic = CreateObject("scripting.dictionary")
    
    
    For Each rg In arr
        'If rg <> "" And Not rg Like "*[\u4E00-\u9FA5]*" Then
        If rg <> "" And IsStringWithoutChinese(rg) Then
            If dic.exists(rg) Then
                dic(rg) = Val(dic(rg)) + 1
            Else
                dic(rg) = 1
            End If
        End If
    Next
    
    
    Sheet2.Range("A2:Z65535") = ""
    Sheet2.Range("A2").Resize(dic.Count, 1) = Application.WorksheetFunction.Transpose(dic.keys)
    Sheet2.Range("B2").Resize(dic.Count, 1) = Application.WorksheetFunction.Transpose(dic.items)
    
    排序
End Sub








Function IsStringWithoutChinese(str) As Boolean
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    
    With regex
        .Pattern = "[\u4E00-\u9FA5]"
        .IgnoreCase = True
        .Global = True
    End With
    
    IsStringWithoutChinese = Not regex.Test(str)
End Function