[已解决]请教：区域去重复且按降序排列的宏

海水鱼

请看：附件

最佳答案

月排行榜 / 总排行榜

grf1973

2015-11-19 09:43

当然，用字典更简洁一点。

1. Sub tt()
   
2.     arr = Range("b5:AL37")
   
3.     Set d = CreateObject("scripting.dictionary")
   
4.     For Each x In arr
   
5.         d(x) = ""
   
6.     Next
   
7.     [an5].Resize(d.Count, 1) = Application.Transpose(d.keys)
   
8.     [an5].Resize(d.Count, 1).Sort key1:=[an5], order1:=xlDescending
   
9. End Sub
   

复制代码

跳转到最佳答案楼层

grf1973

1. Sub tt()
   
2.     arr = Range("b5:AL37")
   
3.     xmin = Application.WorksheetFunction.Min(arr)
   
4.     xmax = Application.WorksheetFunction.Max(arr)
   
5.     ReDim brr(xmin To xmax, 1 To 1)
   
6.     For Each x In arr
   
7.         brr(x, 1) = x
   
8.     Next
   
9.     [an5].Resize(UBound(brr), 1) = brr
   
10.     [an5].Resize(UBound(brr), 1).Sort key1:=[an5], order1:=xlDescending
    
11. End Sub
    

复制代码

grf1973

请看附件。

grf1973

当然，用字典更简洁一点。

1. Sub tt()
   
2.     arr = Range("b5:AL37")
   
3.     Set d = CreateObject("scripting.dictionary")
   
4.     For Each x In arr
   
5.         d(x) = ""
   
6.     Next
   
7.     [an5].Resize(d.Count, 1) = Application.Transpose(d.keys)
   
8.     [an5].Resize(d.Count, 1).Sort key1:=[an5], order1:=xlDescending
   
9. End Sub
   

复制代码