- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
回復 5# junkwei - Option Explicit
- Sub Ex()
- Dim Rng As Range, i As Integer, Ar()
- Set Rng = Range("iv1")
- '進階篩選出"班級"欄不重複的值
- Range("a:a").AdvancedFilter xlFilterCopy, , Rng, True
- ReDim Ar(1 To 3, 1 To 1) '陣列重置
- Ar(1, 1) = "班級"
- Ar(2, 1) = "最大年紀"
- Ar(3, 1) = "最小年紀"
- i = 2
- Do While Rng.Cells(i) <> "" '"班級"欄不重複的值(各班級)
- With Range("a:a")
- .Replace Rng.Cells(i), "=EX", xlWhole '各班級換置為錯誤值
- With .SpecialCells(xlCellTypeFormulas, xlErrors)
- ReDim Preserve Ar(1 To 3, 1 To i)
- Ar(1, i) = Rng.Cells(i)
- Ar(2, i) = Application.Max(.Cells.Offset(, 2))
- Ar(3, i) = Application.Min(.Cells.Offset(, 2))
- .Value = Rng.Cells(i) '錯誤值換置回為各班級
- End With
- i = i + 1
- End With
- Loop
- Rng.EntireColumn.Clear '清除:進階篩選出"班級"欄不重複的值
- Range("g1").Resize(UBound(Ar, 2), UBound(Ar, 1)) = Application.WorksheetFunction.Transpose(Ar)
- 'UBound(Ar, 2)陣列第2維的元素上限值
- 'UBound(Ar, 1)陣列第1維的元素上限值
- End Sub
|
|
發表於 2014-12-4 07:01
| 
