|
- Sub 整理表格()
- Dim r%, i%
- Dim arr
- Dim rng As Range
- tm = Array("序号", "姓名", "工号", "体系", "效果", "四季度体系建设", "体系", "效果", "四季度体系建设", "储蓄帐号")
- With Worksheets("sheet1")
- r = .UsedRange.Find(what:="*", lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
- For i = r To 1 Step -1
- If Not IsNumeric(.Cells(i, 1)) Or Len(.Cells(i, 1)) = 0 Or Application.Sum(.Cells(i, 4).Resize(1, 6)) = 0 Then
- If rng Is Nothing Then
- Set rng = .Rows(i)
- Else
- Set rng = Union(rng, .Rows(i))
- End If
- End If
- Next
- If Not rng Is Nothing Then
- rng.Delete
- End If
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- .Cells(1, 1) = 1
- For i = 2 To r
- If .Cells(i, 1) <> 1 Then
- .Cells(i, 1) = .Cells(i - 1, 1) + 1
- End If
- Next
- With .UsedRange
- .Font.Name = "宋体"
- .Font.Size = 10
- .Font.Bold = False
- .Borders.LineStyle = xlNone
- .HorizontalAlignment = xlGeneral
- .VerticalAlignment = xlCenter
- End With
- r0 = r + 1
- For i = r To 1 Step -1
- If .Cells(i, 1) = 1 Then
- .Rows(i & ":" & i + 4).Insert
- With .Cells(i + 3, 1)
- .Value = "单 位:"
- .Font.Bold = True
- End With
- With .Cells(i + 4, 1).Resize(1, UBound(tm) + 1)
- .Value = tm
- .HorizontalAlignment = xlCenter
- .Font.Bold = True
- End With
- With .Cells(r0 + 5, 1)
- .Value = "合 计"
- .HorizontalAlignment = xlCenter
- .Font.Bold = True
- End With
- With .Cells(r0 + 6, 1)
- .Value = "单位负责人:"
- .Font.Bold = True
- End With
- With .Cells(r0 + 6, 9)
- .Value = "制表人:"
- .Font.Bold = True
- End With
- .Range(.Cells(i + 4, 1), .Cells(r0 + 5, 10)).Borders.LineStyle = xlContinuous
- .Range(.Cells(r0 + 5, 4), .Cells(r0 + 5, 9)).FormulaR1C1 = "=SUM(R[" & i - r0 & "]C:R[-1]C)"
- r0 = i
- End If
- Next
- .Rows("1:3").Delete
- .Columns("a:j").AutoFit
- End With
- End Sub
复制代码 |
|