|
Sub test合并排序删重()
Dim lr&, i%, j%, j1%, m%, t&
Dim arr, brr, crr(1 To 30000, 1 To 10)
Dim sh As Worksheet
Dim mypath$ ', myname$
Application.ScreenUpdating = False
Application.DisplayAlerts = False
t = Timer
mypath = ThisWorkbook.Path & "\"
lr = ThisWorkbook.Sheets(1).Range("x1").End(4).Row
arr = ThisWorkbook.Sheets(1).Range("x1:x" & lr)
For i = 1 To UBound(arr)
Set sh = GetObject(mypath & "集合\" & arr(i, 1) & ".xlsm").Sheets(2)
brr = sh.UsedRange
Workbooks(arr(i, 1) & ".xlsm").Close False
For j = 3 To UBound(brr)
m = m + 1
For j1 = 1 To 10
crr(m, j1) = "'" & Format(brr(j, j1), "000")
Next
Next
Set sh = Nothing
Next
With ThisWorkbook.Sheets(1)
.Range("a3:j30000").ClearContents
.[a3].Resize(m, UBound(crr, 2)).Value = crr
For c = 1 To 10
'排序
.Range(.Cells(3, c), .Cells(30000, c)).Select
.Sort.SortFields.Clear
.Sort.SortFields.Add2 Key:=.Range(.Cells(3, c), .Cells(30000, c)), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange Range(Cells(3, c), Cells(30000, c))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'删重
.Range(.Cells(3, c), .Cells(30000, c)).RemoveDuplicates Columns:=1, Header:=xlNo
Next
.Range("a3").Select
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "汇总完成!汇总了:" & lr & "个工作表;共有:" & m & "行数据。" & vbCr & "用时:" & Format(Timer - t, "0.00") & "秒", vbInformation
End Sub |
评分
-
1
查看全部评分
-
|