|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
再改一下,通用性好一点。- Sub ykcbf() '//2024.12.13
- ReDim brr(1 To 10000, 1 To 5)
- ReDim crr(1 To 10000, 1 To 5)
- col = 3 '//查找的列号
- For Each sht In Sheets
- If InStr("max|min", sht.Name) = 0 Then
- With sht
- r = .UsedRange.Find("*", , -4163, , 1, 2).Row
- arr = .[a1].Resize(r, 5)
- Max = Application.Max(.Cells(1, col).Resize(r))
- Min = Application.Min(.Cells(1, col).Resize(r))
- For i = 1 To UBound(arr)
- If arr(i, col) = Max Then
- m = m + 1
- For j = 1 To UBound(arr, 2)
- brr(m, j) = arr(i, j)
- Next
- End If
- If arr(i, col) = Min Then
- n = n + 1
- For j = 1 To UBound(arr, 2)
- crr(n, j) = arr(i, j)
- Next
- End If
- Next
- End With
- End If
- Next
- With Sheets("max")
- .UsedRange.Offset(1).ClearContents
- .[a2].Resize(m, 5) = brr
- End With
- With Sheets("min")
- .UsedRange.Offset(1).ClearContents
- .[a2].Resize(n, 5) = crr
- End With
- MsgBox "OK!"
- End Sub
复制代码
|
评分
-
2
查看全部评分
-
|