|
本帖最后由 轩辕轼轲 于 2013-12-9 16:56 编辑
- Option Base 1
- Sub 建表()
- Dim dic, maxRow%, sArr, tArr(), i%, x%, j%, tSh As Worksheet, tRng As Range, tShape As Shape
- Set dic = CreateObject("Scripting.Dictionary")
- With Sheets("入分表")
- Application.DisplayAlerts = False
- For i = Sheets.Count To 1 Step -1
- If Sheets(i).Name <> .Name Then Sheets(i).Delete
- Next i
- Application.DisplayAlerts = True
- maxRow = .Cells(.Columns(2).Rows.Count, 2).End(xlUp).Row
- sArr = .Range("B2:B" & maxRow)
- For i = 1 To UBound(sArr, 1)
- If Not dic.Exists(sArr(i, 1)) Then
- x = x + 1
- ReDim Preserve tArr(1 To x)
- tArr(x) = sArr(i, 1)
- dic.Add sArr(i, 1), ""
- End If
- Next i
- For i = 1 To x
- .Copy After:=Sheets(Sheets.Count)
- Set tSh = Sheets(Sheets.Count)
- With tSh
- For Each tShape In .Shapes
- If tShape.Type = msoFormControl Then tShape.Delete
- Next tShape
-
- .Name = tArr(i)
- Set tRng = .Cells(maxRow + 1, 2)
- For j = 2 To maxRow
- If .Cells(j, 2).Value <> tArr(i) Then Set tRng = Union(tRng, .Cells(j, 2))
- Next j
- tRng.EntireRow.Delete
- End With
- Next i
- End With
- Set tSh = Nothing
- Set dic = Nothing
- End Sub
复制代码 适用于各种情况,无损拆分,不改变源表的任何格式。 |
|