|
- Private Sub CommandButton1_Click()
- Dim arr, arr2
- Dim objDic As Object, objDic2 As Object
- Set objDic = CreateObject("scripting.dictionary")
- Set objDic2 = CreateObject("scripting.dictionary")
- Dim i&, j&, k&
- Dim r&, c&, r2&
- Dim str$, key
- Dim t#
- Dim sh As Worksheet, sh1 As Worksheet
- Set sh = Sheets("1")
- Set sh1 = Sheets("2")
- r2 = 1
- t = Timer
- Application.ScreenUpdating = False
- Application.Interactive = False
- Application.EnableEvents = False
- For r = 1 To 9346 Step 9346
- For c = 1 To 29 Step 14
- With sh.Cells(r, c).Resize(9346, 13)
- .SpecialCells(xlCellTypeBlanks).Value = "#"
- arr = .Value
- End With
- For i = 1 To UBound(arr)
- str = ""
- For j = 1 To UBound(arr, 2)
- str = str & arr(i, j) & "@"
- Next
- str = Left(str, Len(str) - 1)
- objDic(str) = objDic(str) + 1
- Next
- For Each key In objDic.keys
- If objDic(key) > 3 Then
- objDic2.Add objDic2.Count + 1, Split(key, "@")
- End If
- Next
- If objDic2.Count Then
- arr2 = WorksheetFunction.Transpose(WorksheetFunction.Transpose(objDic2.items))
- If objDic2.Count = 1 Then
- sh1.Cells(r2, c).Resize(, UBound(arr)).Value = arr2
- Else
- sh1.Cells(r2, c).Resize(UBound(arr), UBound(arr, 2)).Value = arr2
- End If
- End If
- objDic2.RemoveAll
- objDic.RemoveAll
- Next
- r2 = sh1.UsedRange.Rows.Count + 1
- Next
- sh1.UsedRange.Replace "#", ""
- sh.UsedRange.Replace "#", ""
- Application.ScreenUpdating = True
- Application.Interactive = True
- Application.EnableEvents = True
- MsgBox "ok"
- End Sub
复制代码 关于1个筛选项的更正。 |
|