Excelマクロでデータの並べ替えの具体的方法を整理（Excel2003までの並べ替え）

2022年2月6日

Excelマクロでデータの並べ替えの要素を複数指定して行う具体的なやり方を整理しましたので備忘記録代わりに記事にしておきます。

Excel2003までの並べ替えとそれ以降ではやり方が変わる。

Excelマクロでデータの並び替えを行う場合、Excel2003時代？までのマクロのつくり方と、それ以降では書き方が変わるようです。

ただ、どちらのやり方でも並び替えの作業自体は2022年現在も可能です。

で、ざっくりと両方のマクロの書き方を確認してみたところ、個人的には昔のやり方の方が理解しやすかった点や、税理士業務の範囲内で使用するケースを想定した際にExcel2003までの書き方で十分だと感じましたので今回はExcel2003までの並び替えの具体的やり方を整理しようと思います。

データを並び替えるマクロ　並び替え範囲が固定されている場合

今回Excelマクロでのデータの並び替えのやり方を理解するにあたり、下記のようなデータを準備しました↓

このデータに関してマクロで「日付」のデータを第一優先で「昇順」で並び替えを行い、「金額」のデータを第二順位にして「昇順」で並び替えを行うには下記のようなマクロを書くと上手くいきます↓

Sub test0()

'■並べ替えの範囲を指定して日付→金額の順に昇順で並べ替えする

Range("b2:e16").Sort _
Key1:=Range("b2"), Order1:=xlAscending, _
Key2:=Range("c2"), Order2:=xlAscending, _
Header:=xlYes


End Sub

実行するとこんな感じにデータが並び替えができました↓　セルに色を着けている部分に関して日付→金額の順に昇順になっていると思います。

データの並び替えをする際には最低3つの項目を指定するようにすると良いです。

Key
Order
Header

の３つです。

Key1、Key2、Key3で並び替えのキーを指定する

Excelマクロでデータの並び替えをする場合にはrangeオブジェクトのsortメソッドを使用する必要があるそうです。

自分の場合、「rangeオブジェクトのsortメソッド」と言われてもピンとこないので、ざっくりというと、「range(“a1”).sort」みたいな感じで書けばいいということで理解しています。

で、そのsortを使用する際には、いくつか条件を指定する必要があり、その条件の一つが「並び替えのキー」になります。つまりどの項目で並び替えをするかということです。

上の画像でいうと、「日付」「金額」「立替者」「内容」が並び替えのキーになります。

例えば「日付」を並び替えのキーにしたい場合には今回の場合、B２セルに「日付」の記載があるので、key1:=range(“b2”)と指定します。

もし、「金額」を並び替えのキーにしたい場合にはC2セルに「金額」の記載があるのでkey1:=”range(“c2”)”と指定します。

なお、この並び替えのキーに関しては最大３つまで指定することができます。

なので、さきほどのように「日付」のデータを第一優先で「昇順」で並び替えを行い、「金額」のデータを第二順位にして「昇順」で並び替えを行うといったことが可能です。

Order1、Order2、Order3で並び替えの順番（昇順か降順か）を指定する

ここまでで、どの項目を並び替えのキーにするかの設定ができました。つぎのそのデータの並び替えを昇順でやるのか降順でやるのかを指定する必要があります。

それが「Order」になります。

昇順で並び替えをする場合にはOrder１：＝xlAscendingと記載します。

降順で並び替えをする場合にはOrder１：＝xlDescendingと記載します。

なお、Orderに対して何も指定しなかった場合には昇順（xlAscending）を指定したものとして扱われます。

なので、上で紹介したマクロは下記のように省略することも可能です↓

Sub test0()

'■並べ替えの範囲を指定して日付→金額の順に昇順で並べ替えする

Range("b2:e16").Sort _
key1:=Range("b2"), _
Key2:=Range("c2"), _
Header:=xlYes


End Sub

このように省略することでコードがシンプルになるのですが、個人的には省略せずに記載する方がわかりやすさが増すというか、丁寧な感じがしています。この辺は慣れの問題もあると思いますが。

Orderの規定値が昇順（xlAscending）であることを覚えているのであれば省略して問題ないと思います。

Headerで先頭行をタイトル行にするかどうかを指定する

データの並び替えをする際に、データ範囲の先頭行を「タイトル行」として扱うかどうかを指定するには引数「header」に対して設定をおこないます。

データ範囲の先頭行をタイトル行として扱う場合にはheader:=xlyesを指定します。
先頭行をタイトル行として扱わない場合にはheader:=xlnoを指定します。

先頭行をタイトル行として扱うかどうかをExcelに判定させる場合にはheader:=xlGuessを指定します。

なお、マイクロソフトのヘルプページによると、headerに対して何も指定しなかった場合にはxlNoを設定したものとして扱われます。

データを並び替えるマクロ　並び替え範囲が増減する（可変）場合

ここまでのマクロに関しては並び替えの対象となるデータ範囲が固定されている場合には上手くいくマクロでした。

ですが、通常はデータの範囲は増減するのが普通です。データ範囲が可変の場合にはcurrentregionで範囲を取得するとよいです。

Sub test1()

'■並べ替えの範囲が増減しても対応できるためにcurrentregionで範囲指定して
'■立替者→日付の順に昇順で並べ替えする

Range("b2").CurrentRegion.Sort _
Key1:=Range("d2"), Order1:=xlAscending, _
Key2:=Range("b2"), Order2:=xlAscending, _
Header:=xlYes


End Sub

この書き方の場合にはデータの範囲が増減しても問題なく並び替えの対象としたい範囲を取得することができます。

変数を宣言しておいて、宣言した変数に対して、データの並び替え範囲を格納する方法もあります。上とやっていることは同じですが↓

Sub test2()

'■並べ替えの範囲を変数「rng」に格納して
'■立替者→金額の順に昇順で並べ替えする

Dim rng As Range
Set rng = Range("b2").CurrentRegion

rng.Sort _
Key1:=Range("d2"), Order1:=xlAscending, _
Key2:=Range("c2"), Order2:=xlAscending, _
Header:=xlYes


End Sub

並び替えの対象としたい特定のデータ列が移動する場合にはどうすればいいか

データが追加された場合に並べ替えの対象にしたい「タイトル行」が左右に移動するケースも考えられます。

例えばこんな感じで当初C列にあった「金額データ」がデータの追加などによって別の場所（G列）へ変更になった場合を考えます↓

上の画像のようにデータが追加された場合でも問題なく並べ替えをしたいのであればこんなやり方もあるのかもしれません↓

Sub test3()


'■並べ替えの範囲を変数「rng」に格納して
'■並べ替えのキーワードを変数「target」に格納して昇順で並べ替えする

Dim rng As Range
Dim target As Range

Set rng = Range("b2").CurrentRegion
Set target = Cells.Find(what:="金額")


rng.Sort _
key1:=target, _
order1:=xlAscending, _
Header:=xlYes


End Sub

変数「target」を一つ追加してその変数に「金額」と記載されたセルデータを検索させて変数に格納します。

さらにその変数を並べ替えのキーワード(key1)に指定します。ハイライト行の8行目、11行目、15行目がその部分になります。

こうすることで並べ替えの対象にしたいデータが移動した場合でも問題なく並べ替えができると思います。