|
发表于 2013-6-27 18:50
|
显示全部楼层
本楼为最佳答案
我为了方便多次测试,所以将生成的数据放在新建的工作表SHEET1内。
如果一定要放在1112工作表的话,我可以修改代码。
完了,我没有分卷压缩工具,这样吧,我把方法和代码告诉你,你自己试试:
1、在工作表最后新建一个空白工作表,表名无所谓,但顺序一定要正确。
顺序:1112⇒1104⇒新建工作表
2、在1112工作表内随便哪个你感觉方便的位置新建一个CommandButton,加入以下代码
3、运行时间在我的机器上是44秒多,结果生成在新建工作表内- Private Sub CommandButton1_Click()
- t = Timer
- Dim arr, arr1, arr2, i&, j%, k&, l&
- arr = [a1].CurrentRegion
- arr1 = Sheets(2).[a1].CurrentRegion
- ReDim arr2(1 To UBound(arr) + UBound(arr1) - 2, 1 To UBound(arr, 2) + UBound(arr1, 2) - 2)
- For i = 2 To UBound(arr)
- If Right(arr(i, 1), 1) = "X" Or Right(arr(i, 1), 1) = "x" Then arr(i, 1) = Left(arr(i, 1), Len(arr(i, 1)) - 1)
- If Right(arr(i, 8), 1) = "X" Or Right(arr(i, 8), 1) = "x" Then arr(i, 8) = Left(arr(i, 8), Len(arr(i, 8)) - 1)
- Next i
- For i = 2 To UBound(arr1)
- If Right(arr1(i, 1), 1) = "X" Or Right(arr1(i, 1), 1) = "x" Then arr1(i, 1) = Left(arr1(i, 1), Len(arr1(i, 1)) - 1)
- If Right(arr1(i, 8), 1) = "X" Or Right(arr1(i, 8), 1) = "x" Then arr1(i, 8) = Left(arr1(i, 8), Len(arr1(i, 8)) - 1)
- Next i
- l = 1
- For i = 2 To UBound(arr)
- For j = 1 To UBound(arr, 2)
- arr2(l, j) = arr(i, j)
- Next j
- l = l + 1
- For k = 2 To UBound(arr1)
- If arr(i, 8) = arr1(k, 1) Then
- For j = 1 To UBound(arr, 2)
- arr2(l, j) = arr1(k, j)
- Next j
- l = l + 1
- End If
- Next k
- Next i
- Sheets(3).[a1].Resize(UBound(arr2), UBound(arr2, 2)) = arr2
- Erase arr: Erase arr1: Erase arr2
- MsgBox Timer - t
- End Sub
复制代码 |
|