|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub 年级总排名()
Dim sh As Worksheet
Dim ar As Variant
Dim br()
ReDim br(1 To 10000, 1 To 6)
For Each sh In Sheets
If InStr(sh.Name, "班") > 0 Then
ar = sh.[a1].CurrentRegion
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) <> "" Then
n = n + 1
For j = 1 To 5
br(n, j) = ar(i, j)
Next j
End If
Next i
End If
Next sh
With Sheets("年级排名")
r = .Cells(Rows.Count, 1).End(xlUp).Row
If r = 2 Then
r = 3
Else
r = r
End If
.Range("a3:f" & r).Borders.LineStyle = 0
.Range("a3:f" & r) = Empty
.[a3].Resize(n, UBound(br, 2)) = br
.[a3].Resize(n, UBound(br, 2)).Borders.LineStyle = 1
For i = 3 To n + 2
.Cells(i, 6) = Application.Rank(.Cells(i, 5), .Range("e3:e" & n + 2))
Next i
.Range("a2:f" & n + 2).Sort .[e2], 2, , , , , , 1 '第一个数据为1,升序排序,为2,降序排序,第二个数据表示标题行数
End With
End Sub
|
|