|
本帖最后由 zjdh 于 2014-6-12 08:00 编辑
- Sub 用选择文件夹的方式提取A列重复值()
- [A1:A65536].ClearContents
- Dim sh As Worksheet, arr, d As Object, i&, r&, MyPath$
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- Set Fold = CreateObject("shell.application").BrowseForFolder(0, "请选择存放数据的文件夹:", 0, "")
- If Fold Is Nothing Then Exit Sub
- Fpath = Fold.Items.Item.Path
- Application.ScreenUpdating = False
- If Fpath > "" Then
- ReDim arr(1 To 2, 1 To 1)
- MyPath = Fpath & ""
- Myfile = Dir(MyPath & "\*.xls")
- Do Until Myfile = ""
- If Myfile <> ThisWorkbook.Name Then
- Set wk = Workbooks.Open(MyPath & "" & Myfile)
- For Each sh In Sheets
- If Application.CountA(sh.UsedRange) Then
- arr = sh.Range("A1:A" & sh.Range("A65536").End(3).Row)
- For i = 1 To UBound(arr)
- If Not d.exists(arr(i, 1)) Then
- d(arr(i, 1)) = ""
- Else
- If arr(i, 1) <> "" Then d1(arr(i, 1)) = ""
- End If
- Next
- End If
- Next
- wk.Close False
- End If
- Myfile = Dir
- Loop
- End If
- [A1].Resize(d1.Count, 1) = Application.Transpose(d1.KEYS)
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|