|
在目标工作表后新加一个sheet作为辅助。
Sub stat()
For i = 2 To 31
ThisWorkbook.Sheets(1).Cells(i, 8) = WorksheetFunction.CountA(ThisWorkbook.Sheets(1).Rows(i)) - 5
m = ThisWorkbook.Sheets(1).Cells(i, 9).End(xlToRight).Column
ThisWorkbook.Sheets(1).Range(Sheets(1).Cells(i, 9), Sheets(1).Cells(i, m)).Copy
ThisWorkbook.Sheets(2).Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
ThisWorkbook.Sheets(2).Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(m - 8, 1)).RemoveDuplicates Columns:=1, Header:=xlNo
r = ThisWorkbook.Sheets(2).Cells(Rows.Count, 1).End(3).Row
ThisWorkbook.Sheets(2).Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(r, 1)).Sort key1:=ThisWorkbook.Sheets(2).Range("a1"), Order1:=xlDescending, Header:=xlNo
arr = ThisWorkbook.Sheets(2).Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(r, 1))
If r = 1 Then
ThisWorkbook.Sheets(1).Cells(i, 7) = ThisWorkbook.Sheets(1).Cells(i, 9)
Else
ThisWorkbook.Sheets(1).Cells(i, 7) = Join(Application.Transpose(arr), ".")
ThisWorkbook.Sheets(2).Cells.Clear
End If
Next
End Sub
|
|