|
发表于 2013-5-22 09:43
|
显示全部楼层
本楼为最佳答案
- Sub 重新组合()
- Dim lWorksheet As Long
- Dim arr, arrNew
- Dim lLastRow&
- Dim rg As Range
- Dim t#
- t = Timer
- On Error GoTo ErrorHandler
- Application.ScreenUpdating = False
- Application.Calculation = xlCalculationManual
- Application.EnableEvents = False
- For lWorksheet = 2 To Worksheets.Count
- With Worksheets(lWorksheet)
- lLastRow = .Cells(Rows.Count, 2).End(xlUp).Row
- Set rg = .Range(.[a1], .Cells(lLastRow, 55))
- arr = rg.Value
- arrNew = arrCombine(arr)
- If IsArray(arrNew) Then
- rg.ClearContents
- .Range("a1").Resize(UBound(arrNew), UBound(arrNew, 2)).Value = arrNew
- End If
- End With
- Next
-
- Application.ScreenUpdating = True
- Application.Calculation = xlCalculationAutomatic
- Application.EnableEvents = True
- t = Timer - t
- MsgBox "组合完成" & vbCrLf & _
- "一共用时 " & t & " 秒", vbInformation + vbOKOnly
- Exit Sub
- ErrorHandler:
- MsgBox Err.Number & vbCrLf & _
- Err.Description
- End Sub
- Function arrCombine(arr)
- Dim i As Byte, j As Byte, k As Integer
- 'Dim arrNew(1 To 10000, 1 To 55)
- Dim arrNew()
- Dim lArrL&, lArrU&, lArrU2&, lCount&
- On Error GoTo ErrorHandler
- lArrL = LBound(arr)
- lArrU = UBound(arr)
- ReDim arrNew(1 To (lArrU ^ 2 - lArrU) / 2 * 3, 1 To 55)
- lArrU2 = UBound(arr, 2)
- For i = lArrL To lArrU
- For j = i + 1 To lArrU
- lCount = lCount + 1
- For k = 1 To lArrU2
- arrNew(lCount, k) = arr(i, k)
- Next
- lCount = lCount + 1
- For k = 1 To lArrU2
- arrNew(lCount, k) = arr(j, k)
- Next
- lCount = lCount + 1
- Next
- Next
- arrCombine = arrNew
- Exit Function
- ErrorHandler:
- MsgBox Err.Number & vbCrLf & _
- Err.Description
- Err.Clear
- arrCombine = ""
- End Function
复制代码 |
评分
-
查看全部评分
|