|
发表于 2012-2-29 16:35
|
显示全部楼层
本楼为最佳答案
试试:
Sub test()
Dim ar1(), ar2()
With Sheets(4)
.Cells.Clear
r% = 1
For i% = 1 To 3
Sheets(i).[a65536].End(xlUp)(2, 2) = 100
ar1 = Sheets(i).UsedRange.Value
Sheets(i).UsedRange.Sort Sheets(i).[b1], , , , , , , xlYes
ar2 = Sheets(i).UsedRange.Value
r1% = 2
r2% = 2
Do While r1 < UBound(ar2)
If ar2(r1, 2) <> ar2(r2, 2) Then
.Cells(r, 1) = Left(Sheets(i).Name, 1) & ar2(r1, 2) & "班学生考试信息"
.Range("a" & r & ":k" & r).Merge
.Range("a" & r + 1 & ":k" & r + 1) = [{"姓名","班级","考号","考场","教室","","姓名","班级","考号","考场","教室"}]
Sheets(i).Cells(r1, 1).Resize(Int(0.6 + (r2 - r1) / 2), 5).Copy .Cells(r + 2, 1)
r1 = r1 + Int(0.6 + (r2 - r1) / 2)
Sheets(i).Cells(r1, 1).Resize(r2 - r1, 5).Copy .Cells(r + 2, 7)
r = .[a65536].End(3).Row + 1
r1 = r2
End If
r2 = r2 + 1
Loop
Sheets(i).UsedRange = ar1
Sheets(i).[a65536].End(xlUp)(2, 2).Clear
Next
.Cells.HorizontalAlignment = xlCenter
End With
End Sub |
|