|
附件请测试,横向纵向都有汇总涂色。- Private Sub CommandButton1_Click()
- t = Timer
- Dim arr, i&, j%, k&, sh As Byte
- For i = 1 To 5
- arr = Sheets(i).Range(Sheets(i).Cells(1, 1), Sheets(i).Cells(Sheets(i).[a65536].End(3).Row + 1, Sheets(i).[iv1].End(1).Column + 1))
- arr(UBound(arr), 1) = "Count": arr(1, UBound(arr, 2)) = "Count"
- For j = 2 To UBound(arr, 2) - 1
- arr(UBound(arr), j) = Application.Sum(Application.Index(arr, , j))
- Next j
- For k = 2 To UBound(arr)
- arr(k, UBound(arr, 2)) = Application.Sum(Application.Index(arr, k, 0))
- Next k
- Sheets(i).[a1].Resize(UBound(arr), UBound(arr, 2)) = arr
- Sheets(i).Range(Sheets(i).Cells(UBound(arr), 1), Sheets(i).Cells(UBound(arr), UBound(arr, 2))).Interior.ColorIndex = 6
- Sheets(i).Range(Sheets(i).Cells(1, UBound(arr, 2)), Sheets(i).Cells(UBound(arr), UBound(arr, 2))).Interior.ColorIndex = 6
- Next i
- MsgBox Timer - t & "Sec"
- End Sub
复制代码 |
|