"降順ソート" 

"出力例"

降順(大きい値から小さい値の順番)にデータを並び替えろ。

"サンプルマクロ01"

"ヒント・解説01"

Private Function RndBetween(lnum As Long, unum As Long) As Long '乱数生成

Randomize

RndBetween = Fix(Rnd() * (unum - lnum + 1) + lnum)

End Function


Public Sub descendingsort01()

Const n As Byte = 30 '要素数

Dim h As Integer, i As Integer, j As Integer
Dim tmpr As Variant
Dim krng As Range, srng As Range
Dim keyc As Integer, scl As Integer, scr As Integer

Set krng = Range("B1") '比較対象キー
Set srng = Range("B2:G30") 'データ範囲

keyc = krng.Columns.Column 'キーの列番号
scl = srng.Columns(1).Column 'ソート範囲の左端の列番号
scr = srng.Columns.Count 'ソート範囲の右端の列番号

For h = 1 To n 'ソート用データ生成

    Cells(h, 2).Value = RndBetween(1, 10)
    Cells(h, 3).Value = ChrW(RndBetween(12354, 12435)) 'ひらがな
    Cells(h, 4).Value = ChrW(RndBetween(12448, 12530)) 'カタカナ
    Cells(h, 5).Value = Chr(RndBetween(65, 90))  '[A-Z]
    Cells(h, 6).Value = ChrW(RndBetween(945, 969))  '[α-ω]
    Cells(h, 7).Value = ChrW(RndBetween(13312, 40892)) '漢字
    
Next h

For i = 1 To n '降順バブルソート

    For j = i + 1 To n
    
        If Cells(i, keyc).Value < Cells(j, keyc).Value Then
        
            tmpr = Range(Cells(j, scl), Cells(j, scr)).Value
            Range(Cells(j, scl), Cells(j, scr)).Value = Range(Cells(i, scl), Cells(i, scr)).Value
            Range(Cells(i, scl), Cells(i, scr)).Value = tmpr
                       
        End If

    Next j

Next i

Set krng = Nothing: Set srng = Nothing 'オブジェクトの解放

End Sub
	

  1.バブルソート(隣接交換法)により並び替える

  2.特定の列を第一キーとしてソートする   

  ・ChrW()関数 : 特定の文字を返す

     ChrW(65) : "A"を返す, ChrW(97) : "a"を返す

     [A-Z](65-90), [a-z](97-122),
     [Α-Ω](913- 937), [α-ω](945-969)