VBA 去空 去重代码

 

 

 

Sub quchong1()
Dim arr1()

'去空的
For i = Range("a65536").End(xlUp).Row To 1 Step -1
    If Cells(i, 1) = "" Then
       Rows(i).Delete
    End If
Next

'去重
mc = Application.WorksheetFunction.CountA(Range("a:a"))
ReDim arr1(1 To mc)
For j = 1 To mc
  arr1(j) = Cells(j, 1)
Next


Dim dict1 As Object
Set dict1 = CreateObject("scripting.dictionary")

For k = 1 To UBound(arr1)
    dict1(arr1(k)) = ""
Next

m = 1  '必须否则报错
For Each i In dict1.keys
    Cells(m, 5).Value = i
    m = m + 1
Next

End Sub