|
- Sub 分类()
- arr = Range("a1:j" & [a65536].End(3).Row)
- Set d = CreateObject("scripting.dictionary")
- bj = [L1] '比较数
- For i = 1 To UBound(arr) Step 13 '13行为一单元
- For j = 2 To 7 '判断每单元各列各小组第一行是否与比较数相同
- If arr(i, j) = bj And arr(i + 3, j) = bj And arr(i + 6, j) = bj And arr(i + 9, j) = bj Then
- d(j - 1) = d(j - 1) & "," & i '如果相同,记录该单元首行进字典。字典以列数-1为key
- End If
- Next
- Next
- For j = 1 To 6
- If d.exists(j) Then
- Worksheets.Add after:=Sheets(Sheets.Count)
- With ActiveSheet
- .Name = j
- xrr = Split(d(j), ",") '各单元首行组成的数组
- n = 0
- ReDim brr(1 To 13 * UBound(xrr), 1 To UBound(arr, 2))
- For k = 1 To UBound(xrr) '复原各单元首行,并以此开始13行录入数组brr
- i = xrr(k)
- For kk = 0 To 12
- n = n + 1
- For jj = 1 To UBound(arr, 2)
- brr(n, jj) = arr(i + kk, jj)
- Next
- Next
- Next
- .[a1].Resize(n, UBound(arr, 2)) = brr
- .UsedRange.Columns.AutoFit
- End With
- End If
- Next
- Sheet1.Activate
- End Sub
复制代码 |
|