2020/11/09

複数行１データの並び替え

• １．背景
• ２．システム概要
• ３．プログラムの流れ
• 　３－１．並べ替え列追加法
• 　３－２．Collection法
• ４．昇順・降順ダイアログ
• 　４－１．フォームの作成
• 　４－２．フォームモジュール
• 　　４－２－１．起動準備とフォーム起動
• 　　４－２－２．オプションボタンでの選択
• 　　４－２－３．ＯＫボタン・キャンセルボタンのクリック
• ５．並べ替え列追加法（ワークシートモジュール）
• 　５－１．シートレベル定数の宣言
• 　５－２．ダブルクリックによるイベントプロシージャ
• 　５－３．並べ替え列追加と削除
• 　５－４．並べ替え列へデータの追加
• 　５－５．データ範囲取得
• 　　５－５－１．通常のデータ範囲取得
• 　　５－５－２．データ範囲取得（データ範囲に空白行がある場合）
• ６．Collection法（ワークシートモジュール）
• 　６－１．シートレベル定数の宣言、Collectionの宣言
• 　６－２．ダブルクリックによるイベントプロシージャ
• 　６－３．Collection値の読み取りと並べ替え
• 　６－４．Collection値の貼付け
• 　６－５．データ範囲取得
• ７．最後に

１．背景

データの項目が多い場合、ワークシートの列を多く使う横長の表になってしまいます。いくら横スクロールバーが有るからと言っても１行が横に長い表は見難いと思います。
その解決策の１つである「複数行で１つのデータ」は、紙や画面に表示した時の見易さから世の中では多用されています。また以前流行したカード型データベースの影響もあるのかもしれません。

しかし「複数行１データ」をExcelで扱うのは非常にやっかいです。他のサイトを見ても「止めなさい」という意見がほとんどです。しかし既存のデータを活用することも重要ですので、今回は「複数行１データ」を各項目で並び替える手法について紹介します。

２．システム概要

図２－１のように複数行で１つのデータがあるとします。１～２行目がタイトル行（黄色く塗ったセル）になっており、そのタイトルのどこかのセル（図２－１では「ふりがな」セル）をダブルクリックすると、ダイアログボックスが起動します。

図２－１

ダイアログボックスで、「昇順」か「降順」を選択し「OKボタン」をクリックすることで、図２－２のように並び替えが実行されます。

図２－２

複数行を同時に並び替えする方法は、色々考えられます。一般的なものは「データの横に列を追加し、並び替える文字と連番を記入」するものです。
他には、複数行１データを１行１データにしてから並び替え後、再び複数行に戻す方法も考えられます。
またデータを配列に入れ、配列内で並び替えをする方法が考えられます。しかし私もトライしたのですが、あまりにも大変なので途中で断念しました。
またまた、検索データのある行を残して他の行を非表示にした後並べ替えると「表示行のみが並び替わる」のですが、残された非表示の行の移動方法がうまく思いつかず、こちらも断念しました。

ということで今回は、「並べ替え列追加法」と、配列を値とするCollectionオブジェクトを使用した「Collection法」の２つを紹介します。
どちらの方法にしても、外観・操作法は図２－１、図２－２で同じです。

３．プログラムの流れ

３－１．並べ替え列追加法

図３－１はサンプルファイルの「２行１データ」です。Ｇ列・Ｈ列はプログラムから検索のためのデータを書きこんだ途中段階で、実際には画面更新を停止させているので見えない画面です。黄色の部分がタイトル行になります。

図３－１

プログラムの流れは図３－２の通りです。まず、タイトルセル部にはダブルクリックのイベントプロシージャが設定してあり、タイトルのどれか（図３－１では「ふりがな」）をダブルクリック①すると、マクロが作動し、データ範囲の右側（ここではＧ列・Ｈ列）に２列分の列追加②をします。
次に各データごとに、「並べ替えワード③」を追加した列にコピーします。また「連番④」も記入します。
並べ替え列の準備が完了したら、「並べ替えワード③」をKey1、「連番④」をKey2にして並べ替えを実行します。
最後に、追加したＧ列・Ｈ列を削除します。

図３－２

３－２．Collection法

データをまとめて管理するやり方として、Excelには「配列」「Collection」「Dictionary」が用意されています。
今回データの集まりを扱うに当たり、どれを使うのが効果的か、図３－３で考えてみます。
（Collection、Dictionary については「CSVファイルでデータを読み書きする月間予定表」も参照下さい）

データのかたまりを順番を付ける際のメリット・デメリット

手法	メリット	デメリット
配列	配列へのデータ取り込みは、 範囲指定のみで簡単に出来る	並び替えるためには、 一度別の配列に仮置きする等が必要
Collection	1ﾃﾞｰﾀずつ収めた配列をCollectionの値に出来る どの位置に挿入するか指定可能	値の一括削除のコマンドが無い
Dictionary	1ﾃﾞｰﾀずつ収めた配列をDictionaryの値に出来る 値の一括削除が出来る	(Key設定必須の為)Keyに並び順を設定するとして､ 入替え時には他の値のKey値も変更要

図３－３

以上の比較表から「Collection」を使い「１つのデータを配列にした上でCollectionの値」とし、「順番を付けながらCollectionに登録」することにしました。
尚、CollectionにもKeyを登録することは可能であり、並び替え用のワードをKey登録すると便利そうでしたが、データ側で並び替えワードが重複している可能性がある（例えば、出身地が同じとか、性別が同じとか）ため、今回はKeyを使用しませんでした。

Collection法で並び替えをするイメージを図３－４に示しました。

図３－４

プログラムとしての流れも図３－５に示しました。タイトルセル部にはダブルクリックのイベントプロシージャが設定してあり、タイトルのどれか（図３－４では「ふりがな」）をダブルクリック①すると、マクロが作動します。（ここまでは、並べ替え列追加法と同じ）
シート上のデータを１つずつ配列にし、それをCollectionの値として登録していきます。Collection登録時にはインデックスを指定しない場合には最終のインデックス番号が付与されますが、既存のCollectionのインデックスの前後を指定できる機能もあります。
そこで登録前に既存のCollectionのデータの「並び替えワード」と比較を行い、昇順に並ぶ位置にインデックスを指定しながら登録を進めていきます。（この場合、割り込まれた他のCollectionのインデックスは、自動的に振られ直されます）

全データがCollection化されると、データは「並び替えワード」の昇順でインデックス番号が並んでいることになります。
そこで、タイトルセルのクリック時①に同時に指定した並び順が「昇順」の場合には、インデックスの順番通りにシート上に配列を貼り付けていきます。尚「降順」を選んでいた場合には、インデックスを逆から貼っていきます。

図３－５

４．昇順・降順ダイアログ

並び替えには「昇順」と「降順」があります。
データを並べているサイトを見てみると、図４－１のような感じに「昇順・降順ボタン」を設けているところが多いようです。
図４－１

このような仕様にすることはExcelでも可能ですが、かなり面倒なので、今回は「ダブルクリックで項目を感知」し、「ダイアログで昇順・降順を選択」する仕様にしました。
なお、「並び替え列追加法」「Collection法」の両方で同じダイアログ（UserForm1）を使います。

４－１．フォームの作成

フォーム画面設計は、図４－２のように「オプションボタン」で昇順・降順を選択し、並べ替え実行用のＯＫボタンとキャンセルボタンを配置しています。ボタン表面の文字は、ダイアログ起動時にプログラムで書くようにしています。

図４－２

４－２．フォームモジュール

今回のシステムは、ワークシート上のデータを並び替えするものです。並び替えには「昇順」「降順」がありますが、ユーザーがダイアログ上でどちらを選択したかの情報が、ワークシート側での並び替え実施の時に必要になります。
フォーム側とワークシート側で情報をやり取りする方法はいくつか考えられます。
　１．共通のパブリック変数を使う
　２．セルを情報の受け渡し場所にする
　３．フォームのLabelやTextBoxを情報の受け渡し場所にする
　４．フォーム内のSubプロシージャを引数付きで呼び出し、その引数で情報を受け渡す
　５．フォーム内の関数プロシージャを呼び出し、その戻り値で情報を受け渡す
今回は「５」の方法を使用します。

４－２－１．起動準備とフォーム起動

ユーザーが「昇順」「降順」のどちらを選択したかは変数SelectOption の値で保存されます。そしてワークシート側から呼び出される関数プロシージャ「UF1_Start」は、その変数SelectOptionの値を戻り値としてワークシート側に戻します。
ですので変数SelectOption はモジュールの中のみで生きていれば良い値ですので、フォームモジュールレベルの変数として、図４－３の２行目で宣言します。

なお変数SelectOption の値は、「１＝昇順を選択」「２＝降順を選択」「０＝キャンセル」を表すことにします。
（値を１，２にしたのは、Sortメソッドのorderに設定する値が「１＝昇順」「２＝降順」であることから決めました。）

1. '==========　⇩(1)　フォームレベル変数設定　====================
2. Dim SelectOption As Long
4. '==========　⇩(2)　起動時のフォーム初期化　====================
5. Private Sub UserForm_Initialize()
6. 　Me.OptionButton1.Value = True
7. 　SelectOption = 1
8. 　Me.CommandButton1.Caption = "Sort実行"
9. 　Me.CommandButton2.Caption = "Cancel"
10. End Sub
12. '==========　⇩(3)　シート側から呼ばれるフォーム起動プロシージャ　============
13. Function UF1_Start()
14. 　Me.Show
15. 　UF1_Start = SelectOption
16. End Function

図４－３

フォームが最初に起動する際には５～１０行目のInitializeイベントが発生します。
６行目ではOptionButtn１（昇順）をTrueにします（OptionButtn１を選択していることになる）。
それと同時に、「どちらを選択しているかを表す変数SelectOption」に「１（昇順）」を代入します。

６行目はOptionButton１のValue値を変更していますので、それを実行した時点でOptionButton１のClickイベントが発生し、図４－４の１８～２０行目が実行されます。ですので本当は７行目が無くても大丈夫なのですが、「OptionButtonを変更したら変数SelectOptionの値を変更する」ことを強調するため、６行目と７行目はセットにしました。

なお、フォームはHideで閉じています（図４－５の２８行目他）ので「並び替え順序は保存」され、次にダイアログを起動した時には１つ前に選択した「並び替え順序」でまず表示されます。
もし「最初に表示されるのは、必ず昇順」にしたい場合は、「Activeイベントプロシージャ（新規作成）に、６～７行目を移動」させて下さい。

８～９行目では、CommandButtonの表面に文字を記入してます。

ワークシート側から呼び出される関数プロシージャ「UF1_Start」が１３～１６行目で、呼び出されるとすぐに自分自身（UserForm1）を表示します（１４行目）。
フォームモジュールレベル変数SelectOptionの値は起動時「１（７行目で設定）」ですが、ダイアログ上でユーザーが操作をし変更した場合は変数SelectOptionに反映されます。その反映された変数SelectOptionを、１５行目で関数の戻り値に設定し、ワークシート側へ「ユーザーが昇順・降順のどれを選択したか」の情報を渡します。

今回は「変数SelectOption」を使って「昇順」「降順」のどちらの状態になっているかを記憶していますが、このような変数を使わずに「ワークシート側に戻す段階でOptionButton１のValue値を確認し、Trueなら１をFalseなら2を戻す」方法も考えられます。 但し、キャンセルボタンをクリックした時にも「どちらかを選んだ」事になってしまいますので、キャンセルボタンやダイアログ右上×印のイベントプロシージャには、ダイアログを閉じる前に「２つのOptionButtonのValue値をFalse」にし、戻す値も「どちらもFalseだったらゼロを戻す」ような処理が必要になります。

４－２－２．オプションボタンでの選択

「昇順」「降順」のOptionButtonをクリックした時に発生するイベントプロシージャが図４－４です。
選んだ側のボタンのValue値がTrue（先頭の白丸が黒丸に変更）になった際に発生するイベントですので、変数SelectOptionにも同時に値を代入します。（１９行目、２４行目）

17. '==========　⇩(4)　「昇順」ボタンをクリックした時　====================
18. Private Sub OptionButton1_Click()
19. 　SelectOption = 1
20. End Sub
22. '==========　⇩(5)　「降順」ボタンをクリックした時　====================
23. Private Sub OptionButton2_Click()
24. 　SelectOption = 2
25. End Sub

図４－４

４－２－３．ＯＫボタン・キャンセルボタンのクリック

「ＯＫ」ボタン「キャンセル」ボタン、および「ダイアログ右上の×印」をクリックした時のイベントプロシージャが図４－５です。

26. '==========　⇩(6)　「ＯＫ」ボタンをクリックした時　====================
27. Private Sub CommandButton1_Click()
28. 　Me.Hide
29. End Sub
31. '==========　⇩(7)　「キャンセル」ボタンをクリックした時　====================
32. Private Sub CommandButton2_Click()
33. 　SelectOption = 0
34. 　Me.Hide
35. End Sub
37. '==========　⇩(8)　ダイアログ右上×印をクリックした時　====================
38. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
39. 　SelectOption = 0
40. End Sub

図４－５

まず「ＯＫ」ボタンの場合は、２８行目でダイアログをそのまま閉じる（Hide＝隠す）ことで図４－３の１５行目に制御が移り、呼出し元のワークシート側へ「ユーザーが選択した昇順・降順の数値」が渡ることになります。

「キャンセル」ボタンの場合は、３３行目で変数SelectOptionに「キャンセルの意味である値ゼロ」を代入します。
そしてダイアログを閉じ（Hide）て図４－３の１５行目に制御が移り、呼出し元のワークシート側へ「ユーザーがキャンセルを選択した」値が渡ることになります。

ダイアログ右上×印をクリックした時には「QueryClose」イベントが発生しますので、３９行目で変数SelectOptionに「キャンセルの意味である値ゼロ」を代入します。
QueryCloseイベントの第１引数にCancelがありますが、これを使って「Cancel = True」としていませんのでダイアログは閉じられます。ですので、３４行目のような「Me.Hide」は QueryClose には必要ありません。

ここで使っているHide（隠す）では無く、本当にフォームを閉じる（Unload Me）を実行してしまうと、ユーザーが選択した値が戻せなくなるだけでなく、図４－６のようなエラーが発生してしまいます。 これは、ワークシート側からフォームモジュール上の「関数プロシージャ」を呼び出しているためです。 図４－６ もしメモリー消費を抑える等の理由でUnloadを使うのであれば、『ワークシートモジュール側・標準モジュール側からは「UserForm1.Show」でフォームを呼び出し、共有の変数など（含：シート上のセル）で値を受け渡す』ような手法にすることで、正常にフォームを閉じる（Unload）ことが出来ます。

５．並べ替え列追加法（ワークシートモジュール）

図３－１、図３－２の「並べ替え列追加」による並べ替えについて説明します。尚、サンプルファイルでは「Sheet１」になります。

５－１．シートレベル定数の宣言

タイトル部のセル範囲を確定しておく必要があるので、定数宣言を先頭の宣言部で行っています。

41. '==========　⇩(9)　シートレベル定数の宣言　====================
42. Const titleStartRow = 1
43. Const titleEndRow = 2
44. Const titleStartCol = 1
45. Const titleEndCol = 6

図５－１

タイトル部の位置と定数の関係は図５－２の通りです。この定数値は範囲設定に使用するだけでなく、ダブルクリックしたセル位置と並び替えワードの位置を結び付ける時にも使用するものなので、数値を得られるR1C1法で位置を表しています。

図５－２

５－２．ダブルクリックによるイベントプロシージャ

ワークシート上のどこかのセルをダブルクリックすると、図５－３のBeforeDoubleClickイベントが発生します。

46. '==========　⇩(10)　タイトル行ダブルクリック時イベント　====================
47. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
48. 　If Not Intersect(Target, Range(Cells(titleStartRow, titleStartCol), Cells(titleEndRow, titleEndCol))) Is Nothing Then
49. 　　Dim ansUF1 As Long　　　　　　　　　　 'ダイアログの戻り値(昇順･降順･キャンセル)
50. 　　Dim DataRange As Range　　　　　　　 '←データ範囲
51. 　　Dim DataWidth As Long　　　　　　　　 'データ範囲の幅
52. 　　Dim DataStep As Long　　　　　　　　　 '1データの行数
53. 　　Dim keyRow As Long　　　　　　　　　　 '選択項目のタイトル部内の位置（縦方向）
54. 　　Dim keyCol As Long　　　　　　　　　　 '選択項目のタイトル部内の位置（横方向）
55. 　　Cancel = True　　　　　　　　　　　　　　 'ダブルクリックによるセル編集を中止
57. 　　ansUF1 = UserForm1.UF1_Start　　　　　 'ダイアログ表示
58. 　　If ansUF1 = 0 Then Exit Sub　　　　　　 'キャンセルの時は抜け出す
60. 　　Set DataRange = DataArea　　　　　　　 '←データ範囲をRange型で取得（右端列加工前に取得）
61. 　　If DataRange Is Nothing Then　　　　　 'データが存在しない場合は抜け出す
62. 　　　MsgBox "データがありません"
63. 　　　Exit Sub
64. 　　End If
66. 　　DataWidth = titleEndCol - titleStartCol + 1 　　　　　　　　　　　 'データの幅
67. 　　DataStep = titleEndRow - titleStartRow + 1 　　　　　　　　　　　 '１データの行数
68. 　　keyRow = Target.Row - titleStartRow + 1 　　　　　　　　　　　　　 '選択項目の縦方向位置
69. 　　keyCol = Target.Column - titleStartCol + 1 　　　　　　　　　　　 '選択項目の横方向位置
70. 　　Set DataRange = DataRange.Resize(Int((DataRange.Rows.Count - 1) / DataStep + 1) * DataStep)
72. 　　Application.ScreenUpdating = False
73. 　　　Call AddCol2(DataRange) 　　　　　　　　　　　　　　　　　　　　　　　　　 'データ範囲の右側に２列追加
74. 　　　Call WordNumAdd(DataRange, keyRow, keyCol, DataStep) 　　　　　 '追加した２列に、並び替えﾜｰﾄﾞと連番を記入
76. 　　　DataRange.Resize(, DataWidth + 2).Sort _
77. 　　　　　　Key1:=DataRange(1).Offset(, DataWidth), order1:=ansUF1, _
78. 　　　　　　Key2:=DataRange(1).Offset(, DataWidth + 1), order2:=1 　　　　　 '追加２列で並び替え実施
80. 　　　Call DelCol2(DataRange) 　　　　　　　　　　　　　　　　　　　　　　　　　 '追加した２列を列削除
82. 　　Application.ScreenUpdating = True
83. 　　Set DataRange = Nothing
84. 　End If
86. End Sub

図５－３

４８行目は「タイトル部分をダブルクリックしたか」を判断する部分です。
「Intersect(セル範囲A , セル範囲B) 」は、セル範囲Aとセル範囲Bの重なっているセル範囲を返す関数で、「Not Intersect(セル範囲A , セル範囲B) Is Nothing」は「セル範囲Aとセル範囲Bが重なっている部分が存在する場合にTrue」となります。
４８行目でのセル範囲Aはダブルクリックしたセル、セル範囲Bはタイトル部ですので、If文は「クリックしたセルがタイトル部だったら」という意味になります。

５５行目では、第二引数の「Cancel」にTrueを代入することで「ダブルクリックによるセル編集を中止」にします。
「セル編集を中止」にしなくても（＝５５行目が無くても）並び替えは実行されますが、並び替え完了した後でも「タイトル部のセルを編集している状態」になるのは、ユーザーに誤解を与える事になると思われるので「Cancel = True」を実行しています。
なお、本当にタイトル部を編集したい場合は、「F2キーを押して編集モード」にして下さい。

５７行目はUserForm1の中の「UF1_Start」関数プロシージャを呼び出し、ユーザーが選択した並び順又はキャンセルを値として変数ansUF1で受け取ります。

標準モジュールに書かれたプロシージャ以外は、呼び出す時に「そのプロシージャの場所を示すオブジェクト名を付ける」必要があります。表にすると図５－４の様になります。 図５－４ ブック以外は複数のモジュールを作ることが出来ますので、例えばフォームモジュールから異なるフォームモジュールのプロシージャを呼び出す時でもオブジェクト名を付ける必要があります。 但し、自モジュール内に呼び出すプロシージャが存在する場合には、オブジェクトを指定する必要はありません。 また標準モジュール内のプロシージャは、どこからでもそのまま呼び出せます。しかし同じ名前のプロシージャが、呼び出すモジュール内と標準モジュール内の両方に存在するような場合には、自モジュールにあるプロシージャを呼び出します。もし標準モジュールの方のプロシージャを呼び出したいのであれば、オブジェクトを付けて呼び出します（図５－５）。 図５－５

５７行目でダイアログからの戻り値を受取った変数ansUF1が、ゼロ（＝キャンセル）だった場合にはイベントプロシージャを抜け出し、並べ替え処理を中止します。（５８行目）

６０行目ではDataArea関数（図５－１７）を呼び出し、戻り値として「データ範囲」を取得し、変数DataRangeに代入します。
しかしデータ行が無かった場合には「Nothing」を戻すことになっているため、６１行目で判断し６２行目でコメントを出して並べ替えを終了（６３行目）します。

６６～６９行目は、データ範囲の諸元（図５－６）を変数に代入しています。変数を使わずに右辺の式を７２行目以降で使用しても良いのですが、式を分かり易くするために置き換えています。

図５－６
この中で６６行目のDataWidthは「titleEndCol - titleStartCol + 1」としていますが、「DataRange.Columns.Count」としてもＯＫです。この「データ範囲の幅」が使える理由は、今回「DataArea関数」で得られるデータ範囲が「titleStartCol から titleEndCol の幅」になるように範囲調整しているからです。

７０行目の「Set DataRange = ･･･」は、６０行目で取得したデータ範囲（DataRange）の補正をしています。
データが各行とも埋まっていれば問題無いのですが、例えば図５－７のように１行だけデータが無かった場合には、中途半端なデータ範囲が取得されてしまいます。

図５－７

このままの状態で並び替えワードを記入してしまうと、図５－８のように「１つ不足した状態」になります。

図５－８

このまま並び替えを実行すると、図５－９のように「１行欠けたデータが複数行データの中に混じり込む」ことになり、気が付かないでいると「データ項目が上下入れ替わってしまう」ような現象が発生します。

図５－９

これを防ぐために７０行目では、欠けている行も含めたデータ範囲に修正をします。
補正する総行数は、「総行数（DataRange.Rows.Count）を１データ行数（DataStep）で割り、その結果を切り上げしてから再度１データ行数（DataStep）を乗ずる」ことで求められます。これを実現するには、以下の２つの式が考えられます。
　(1) Int((DataRange.Rows.Count - 1) / DataStep + 1) * DataStep
　(2) Int( -1 * DataRange.Rows.Count / DataStep ) * -1 * DataStep
どちらを使ってもＯＫですが、７０行目には(1)の式を載せています。
なお(2)の式は「Intは小さい方の整数にする」ことを利用し、一度マイナス値にしてIntで丸めた後、再度プラスにしています。
ちなみに「データが行落ちする事は絶対にない」のであれば、７０行目は不要です。

７３～８０行目では「列追加→並び替えワード記入→並び替え→列削除」という実作業をしています。セル上のデータ変更がありますので、画面ちらつき防止と処理速度向上のために、７２行目で画面更新停止をし８２行目では再び復活させています。

７３行目では「AddCol2」プロシージャを呼出し、データ範囲の右側に２列分の列追加をしています。追加列の位置特定時にデータ範囲の右端位置が必要なため、引数として「DataRange：データ範囲」を渡します。
７４行目では「WordNumAdd」プロシージャを呼び出し、７３行目で追加した列の１列目に「並び替えワード」を記入し２列目に「上からの連番」を記入します。
記入位置を定めるために、第一引数として「DataRange：データ範囲」を渡し、第２～３引数として「各データの中の並び替えワードの位置」、第４引数として「何行で１データか」を渡しています。

次に並び替えのキーですが、第一キーには追加列の「並び替えワード」列を指定し、第二キーに追加列の「連番」列を指定します。
その位置（Key1及びKey2の値）は「文字列の範囲名、または Rangeオブジェクト」で指定しますが、今回はRangeで指定しています。
図５－１０のように「データ範囲の左上角のセル位置（データ範囲のインデックス１番）」を基準にOffsetでセル位置を移動させています。
なお、指定するキーの位置は「キー列であればどこでも良い」ようです。例えば図５－１０でのKey1は「Ｇ３セル」を指示していますが、Ｇ１セルやＧ２０セル（＝並び替え範囲以外）でも正しく並び替えをしてくれます。

図５－１０
また並べ替え順序を指定する引数「order」には、「昇順＝１」「降順＝２」のどちらかを指定します。
７７行目のorder1には、５７行目でダイアログから受け取った値（ansUF1）を指定しています。このorderに直接指定出来るように、ダイアログで昇順を選択したら１に、降順を選択したら２になるように戻り値を決めています。
なお７８行目の「order2」の方は「必ず昇順でないと、１つのデータの中での順番が狂ってしまう」ために、１を設定しています。

並び替えが完了したら、７３行目で追加した２列を列削除します。（８０行目）
８３行目では、役目を終えたDataRangeオブジェクトを解放します。

５－３．並べ替え列追加と削除

並び替えのための列追加が図５－１１のAddCol2プロシージャで、図５－３の７３行目から呼び出されます。
また追加した列を削除するのがDelCol2プロシージャで、図５－３の８０行目から呼び出されます。

87. '==========　⇩(11)　並べ替え列追加　====================
88. Private Sub AddCol2(DataRange As Range)
89. 　Me.Columns(DataRange.Column + DataRange.Columns.Count).Resize(, 2).Insert Shift:=xlShiftToRight
90. End Sub
92. '==========　⇩(12)　並べ替え列削除　====================
93. Private Sub DelCol2(DataRange As Range)
94. 　Me.Columns(DataRange.Column + DataRange.Columns.Count).Resize(, 2).Delete Shift:=xlShiftToLeft
95. End Sub

図５－１１

どちらのプロシージャも、引数にはデータ範囲であるDataRangeを受け取ります。
そのデータ範囲の右外側の２列は図５－１２の①～⑤の手順でを取得します。そして８９行目ではInsertメソッドで列追加し、９４行目ではDeleteメソッドで列削除します。

図５－１２

また列追加・削除後にセルを動かす方向（引数：Shift）ですが、８９行目では「xlShiftToRight」、９４行目では「xlShiftToLeft」としています。
これは図５－１３の通り「右へシフト」「左へシフト」と言う意味ですが、指定している範囲が「列」ですので、引数Shiftが未指定でも「右へ」「左へ」シフトされます。しかも「xlShiftDown」等の間違った設定を仮にしても、ちゃんと「右へ」「左へ」シフトされます。
つまり「列指定で列追加・列削除の時は、Shift引数は無視される」ことになります。
今回は明示的にShift引数を付けています。

図５－１３で、方向を表す値をまとめています。
例えばセル削除時に「xlShiftToLeft」を使うべきところで「xlToLeft」を使用しても、値は同じですので正常に動きます。しかし「削除時には削除用の値を使い、セル移動時にはセル移動用の値を使う」という正しい値の使い方が、バグを見つける第一歩になると思います。

列挙体	名前	値	内容
XlInsertShiftDirection 挿入後どうシフトするか	xlShiftDown	-4121	セルを挿入後、下に伸ばす
	xlShiftToRight	-4161	セルを挿入後、右に伸ばす
XlDeleteShiftDirection 削除後どうシフトするか	xlShiftToLeft	-4159	削除後、セルは左にシフトする
	xlShiftUp	-4162	削除後、セルは上にシフトする
xldirection 移動する方向	xldown	-4121	下へ
	xlToLeft	-4159	左へ
	xlToRight	-4161	右へ
	xlup	-4162	上へ

図５－１３

５－４．並べ替え列へデータの追加

データ範囲の右側に追加した列に、「並べ替えワード」と「連番」を記入するのが図５－１４です。
引数として、データ範囲（DataRange）、各データの中の並び替えワードの縦方向位置（keyRow）、横方向位置（keyCol）、１データの行数（DataStep）の４つを受取ります。

96. '==========　⇩(13)　並べ替えデータの追加　====================
97. Private Sub WordNumAdd(DataRange As Range, keyRow As Long, keyCol As Long, DataStep As Long)
98. 　Dim i As Long　　　　　　 'データ範囲中の先頭からの行位置
99. 　Dim DataNo As Long　　　 'データ範囲中の何番目のデータかを表す変数
101. 　For i = 1 To DataRange.Rows.Count
102. 　　DataNo = Int((i - 1) / DataStep) + 1
103. 　　DataRange.Cells(i, 1).Offset(, DataRange.Columns.Count) = _
104. 　　　　　　　　　　DataRange.Cells(keyRow + (DataNo - 1) * DataStep, keyCol)
105. 　　DataRange.Cells(i, 1).Offset(, DataRange.Columns.Count + 1) = i
106. 　Next i
107. End Sub

図５－１４

まず１０１行目のFor～Nextで、データ範囲（DataRange）の先頭行から最終行までを回していきます。
１０２行目では、変数ｉで表された行のデータの「データ番号」を計算します。計算方法は図５－１５のように、行位置を１データの行数（DataStep）で割った商を求めるやり方です。

図５－１５

１０３～１０４行目は、追加した列の左側列に代入する部分です。
まず１０３行目の左辺は「データ範囲を基準としてCells(行,列)で求めたセル位置（図５－１６のＡ列の各セル位置）」から、横にOffsetで移動させて「並び替えワードを代入するセル（図５－１６のＧ列）」の位置を特定しています。

一方１０４行目の右辺は、並べ替えワードの位置を示しています。
１つのデータ範囲のRangeオブジェクトを仮に[１つのデータ範囲]とすると、その１つのデータ範囲（図５－１６では A3:F4）の中での並べ替えワード（図５－１６では「ひらがな」）の相対的位置は「Cells(KeyRow,KeyCol)」で表されます
この相対的位置を１０２行目で得たデータ番号（DataNo）を使って、絶対的位置を求めています。
この式により、並べ替えワードがデータ行数分だけ並んで記入できます。

図５－１６

１０５行目は「連番」の記入です。左辺は１０３行目の時の１つ隣（図５－１６のＨ列）で、記入する値は「データ範囲の行番号」です。
この連番は１から始まらなくても徐々に大きくなれば良いので、例えばワークシートの行番号にしてもOKです。

５－５．データ範囲取得

５－５－１．通常のデータ範囲取得

図５－３の６０行目から呼び出されるのが図５－１７です。この関数プロシージャは、データ範囲を戻します。

108. '==========　⇩(14)　データ範囲取得１　====================
109. Private Function DataArea() As Range
110. 　Dim AllArea As Range　　　　　　　　 'CurrentRegionで取得される「タイトル＋データの範囲」
112. 　Set AllArea = Cells(titleEndRow, titleStartCol).CurrentRegion
113. 　Set DataArea = Range(Cells(titleEndRow + 1, titleStartCol), Cells(AllArea(AllArea.Count).Row, titleEndCol))
115. 　If AllArea(AllArea.Count).Row = titleEndRow Then
116. 　　Set DataArea = Nothing
117. 　End If
118. 　Set AllArea = Nothing
119. End Function

図５－１７

１１２行目は「アクティブセル領域」を取得し、変数AllAreaに代入しています。
アクティブセル領域とは「選択セルから、全ての方向の最初の空白行・空白列までの領域」で、今回のデータでは図５－１８のように「タイトル部＋データ部」を全て含んだ範囲になります。

図５－１８

このアクティブセル領域は「CurrentRegion」プロパティで取得でき、その基準となる選択セルは今回「タイトル部の左下セル（Cells(titleEndRow, titleStartCol)）」としています。ここにした理由は、データ領域に隣接しているセルである事からです。

このアクティブセル領域を元に、図５－１９のようにデータ範囲（変数DataArea）を求めている式が１１３行目です。
今回は「タイトル行が存在し、またタイトル行とデータ範囲との間に空白行が無い」ことを前提にしていますので、このような式にしています。もし、タイトル行が無かったり、空白行が存在したりする場合には「指定フォルダ配下のファイル情報取得」を参考にして下さい。

図５－１９

データが存在する場合は以上の方法で良いのですが、もしデータが無かったら図５－２０のように「データでは無い部分をデータ範囲と誤認」することになってしまいます。
ですので、１１５行目では「AllArea(AllArea.Count).Row = titleEndRow」の時は、関数プロシージャとして「データが無いことを意味するNothing値」を戻すことにしています。

図５－２０

最後に、１１８行目で不要になったAllArea変数を解放しています。

５－５－２．データ範囲取得（データ範囲に空白行がある場合）

通常でしたら図５－１７の考え方で良いと思うのですが、「複数のデータ抜けが繋がってしまった」とか「項目に余裕を取った」ために、図５－２１の様に「データ範囲の中に空白行が出来てしまった」場合には、本来のデータ範囲が取得できない不具合が発生します。
原因は、「CurrentRegion」で取得できる範囲は「空白行・空白列」のところまでのためです。
（ちなみに図５－２１の６行目のデータは、図５－３の７０行目の「データ範囲補正」コードにより、データ範囲に含まれます。）

図５－２１

もしこのようなデータが考えられるのであれば、下記図５－２２の方法でデータ範囲を取得する必要があります。

120. '==========　⇩(15)　データ範囲取得２　====================
121. Private Function DataArea2() As Range
122. 　Dim EndRow As Long　　　　　　 '使用セル範囲の最下行位置
123. 　Dim EndLine As Range　　　　　 '使用セル範囲の最下行範囲
125. 　EndRow = UsedRange.Row + UsedRange.Rows.Count - 1
126. 　Set EndLine = Range(Cells(EndRow, titleStartCol), Cells(EndRow, titleEndCol))
128. 　Do While Application.WorksheetFunction.CountA(EndLine) = 0
129. 　　Set EndLine = EndLine.Offset(-1, 0)
130. 　　DoEvents: DoEvents
131. 　Loop
133. 　If EndLine.Row = titleEndRow Then
134. 　　Set DataArea2 = Nothing
135. 　Else
136. 　　Set DataArea2 = Range(Cells(titleEndRow + 1, titleStartCol), Cells(EndLine.Row, titleendtcol))
137. 　End If
138. 　Set EndLine = Nothing
139. End Function

図５－２２

ここで使用する「UsedRange」は、「ワークシート内の使用しているセル範囲」を取得するプロパティです。
ですので例え見掛けは空白セルであっても、過去にデータが存在したり、書式（含む：文字サイズや罫線等）を変更していれば、「使用している範囲」に含まれます。

まず１２５行目では、図５－２３のようにUsedRangeを使用して「使用範囲の最下行位置（EndRow）」を計算します。
次に、１２６行目で、タイトルの横方向位置（titleStartCol、titleEndCol）から、「使用範囲の最下行範囲（EndLine）」を求めます。

図５－２３

次に１２８～１３１行目のDo～Loop内では、１２９行目で最下行範囲（EndLine）を１行ずつ上にあげています。
そのDo～Loopを回す条件は「While Application.WorksheetFunction.CountA(EndLine) = 0」としており、「最下行範囲内に１つも値が無い間」という意味になります。
この条件は１２８行目の「Do」側に付いているため、「１つでも値があったらDo～Loopが終了」し、終わった後のEndLineの行が「データが存在する最下行位置」ということになります。
（尚、何らかの異常で無限ループになった時にもストップが掛けられるように、１３０行目にDoEventsコードを入れています。）

このように「EndLine範囲を上に移動させている」理由は、「使用されたセル」では無く「値が入力してあるセル」を取得したいためです。
他に「値が入力してあるセル」を取得する方法としては、下方のセルから「End(xlUp)」で取得するやり方がありますが、１０列データがあったら１０列全ての列に対して調査が必要になります。
どの手法が処理速度に有利かは、データ次第だと思います。例えば、罫線だけはずっと下まで設定してあるような場合は「End(xlUp)」で取得する方が早いかもしれません。

１３３～１３７行目は、最下行範囲（EndLine）の位置で場合分けをしています。
まず「EndLine」がタイトル行まで行ってしまう場合（１３３行目）は、データが何も無いことを意味していますので、１３４行目では戻り値としてNothingを代入しています。
それ以外はデータが存在するため、データ先頭行からEndLine行までをデータ範囲として戻り値に代入します。（１３６行目）
最後に不要となったEndLineオブジェクトを解放します。

尚、データ範囲を取得しているコードは、図５－３の６０行目の「Set DataRange = DataArea」ですので、これを「Set DataRange = DataArea2」に変更するだけで「データ範囲取得方法変更」が可能です。

６．Collection法（ワークシートモジュール）

図３－４、図３－５の「Collection法」による並べ替えについて説明します。尚、サンプルファイルでは「Sheet２」になります。

６－１．シートレベル定数の宣言、Collectionの宣言

「並べ替え列追加法」と同様にタイトル部のセル範囲を特定するための定数設定を行うのが１４１～１４４行目です。
また今回は「Collection」を使用しますので、１４５行目でCollectionオブジェクトの宣言を行います。

140. '==========　⇩(16)　シートレベル定数の宣言、Collectionの宣言　================
141. Const titleStartRow = 1
142. Const titleEndRow = 2
143. Const titleStartCol = 1
144. Const titleEndCol = 6
145. Dim UniRange As Collection

図６－１

Collectionの生成方法としては、図６－２のような方法があります。どの方法でもＯＫですが、今回は方法１を使用しました。

		方法１	方法２	方法３
宣言部		Dim UniRange As Collection	Dim UniRange As New Collection
ダブルクリック プロシージャ内	処理前	Set UniRange = New Collection	－	Collection値の削除要
	処理後	Set UniRange = Nothing	Set UniRange = Nothing	ー

図６－２

尚、方法３を採用する場合は、マクロ終了時（＝並べ替え終了時）でもCollection値が残留するため、毎回Collection値の削除工程が必要になります。しかしCollectionオブジェクトには、Dictionaryオブジェクトにあるような「全消去」のメソッドが無いため、For～Nextなどで１つ１つ削除する必要があります。

６－２．ダブルクリックによるイベントプロシージャ

ワークシート上のどこかのセルをダブルクリックすると、図６－３のBeforeDoubleClickイベントが発生します。

146. '==========　⇩(17)　シートレベル定数の宣言、Collectionの宣言　================
147. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
148. 　If Not Intersect(Target, Range(Cells(titleStartRow, titleStartCol), Cells(titleEndRow, titleEndCol))) Is Nothing Then
149. 　　Dim ansUF1 As Long　　　　　　　　　　 'ダイアログの戻り値(昇順･降順･キャンセル)
150. 　　Dim DataRange As Range　　　　　　　　 '←データ範囲
151. 　　Dim DataStep As Long　　　　　　　　　 '1データの行数
152. 　　Dim keyRow As Long　　　　　　　　　　 '選択項目のタイトル部内の位置（縦方向）
153. 　　Dim keyCol As Long　　　　　　　　　　 '選択項目のタイトル部内の位置（横方向）
155. 　　Cancel = True
157. 　　ansUF1 = UserForm1.UF1_Start
158. 　　If ansUF1 = 0 Then Exit Sub　　　　　　 '← 1＝昇順、2＝降順、0＝キャンセル
160. 　　Set DataRange = DataArea　　　　　　　　 '←データ範囲をRange型で取得（右端列加工前に取得）
161. 　　If DataRange Is Nothing Then
162. 　　　MsgBox "データがありません"
163. 　　　Exit Sub
164. 　　End If
166. 　　DataStep = titleEndRow - titleStartRow + 1
167. 　　keyRow = Target.Row - titleStartRow + 1
168. 　　keyCol = Target.Column - titleStartCol + 1
170. 　　Set DataRange = DataRange.Resize(Int((DataRange.Rows.Count - 1) / DataStep + 1) * DataStep)
171. 　　Set UniRange = New Collection
173. 　　Application.ScreenUpdating = False
174. 　　　Call CollectionRead(DataRange, DataStep, keyRow, keyCol)
175. 　　　Call CollectionWrite(DataRange, DataStep, ansUF1)
176. 　　Application.ScreenUpdating = True
177. 　　Set DataRange = Nothing
178. 　　Set UniRange = Nothing
179. 　End If
180. End Sub

図６－３

１４８行目は「並び替え列追加法」の時と全く同じで、「タイトル部分をダブルクリックしたか」を判断する部分です。
「Intersect(セル範囲A , セル範囲B) 」は、セル範囲Aとセル範囲Bの重なっているセル範囲を返す関数で、「Not Intersect(セル範囲A , セル範囲B) Is Nothing」は「セル範囲Aとセル範囲Bが重なっている部分が存在する場合にTrue」となります。
１４８行目でのセル範囲Aはダブルクリックしたセル、セル範囲Bはタイトル部ですので、If文は「クリックしたセルがタイトル部だったら」という意味になります。

１５５行目では、第二引数の「Cancel」にTrueを代入することで「ダブルクリックによるセル編集を中止」にします。
１５７行目はUserForm1の中の「UF1_Start」関数プロシージャを呼び出し、ユーザーが選択した並び順又はキャンセルを値として変数ansUF1で受け取ります。
１５８行目は、その変数ansUF1がゼロ（＝キャンセル）だった場合にはイベントプロシージャを抜け出し、並べ替え処理を中止します。

１６０行目ではDataArea関数（図５－１７）を呼び出し、戻り値として「データ範囲」を取得し、変数DataRangeに代入します。
しかしデータ行が無かった場合には「Nothing」を戻すことになっているため、１６１行目で判断し１６２行目でコメントを出して並べ替えを終了（１６３行目）します。

１６６～１６８行目は、データ範囲の諸元（図６－４）を変数に代入しています。

図６－４

１７０行目の「Set DataRange = ･･･」は、１６０行目で取得したデータ範囲（DataRange）の補正をしています。
データ範囲の各行ともデータが埋まっていれば問題無いのですが、例えば図５－７のように１行だけデータが無かった場合には、中途半端なデータ範囲が取得されてしまいます。

図６－５

そこで、１６０行目で取得した「データ範囲の行数」と「１データの行数（＝タイトル行の行数）」から「正しいはずの行数」を算出し、不足しているならば「データ範囲を下方向に伸ばしている」のです。

但し今回のCollection法では「並び替え列追加法のような致命的な不具合」は発生しません。図６－７の１８９行目、及び図６－１２の２１０行目・２１６行目で説明しますが、「１つのデータの先頭行とデータの行数」でデータ範囲を取得するため、最終行が空白行でも「空白のままデータに取り込み」ます。またデータ貼付け時も同様なので問題は発生しません。
ただし、取り込んだデータを貼り付けると「データ範囲の中に空白行が存在」することになってしまうため、次回並び替えを行う時に「空白行より下のデータが無視」されてしまいます。
ですので、もし空白行が出来る可能性のあるデータでしたら１７０行目は必須ではありませんが、図６－１４のUsedRangeを使用したデータ範囲取得は必須となります。

それぞれの手法での「データ範囲の中に空白行が存在」する場合の対応方法を図６－６にまとめました。
ですので、今回Collection法では１７０行目は必須ではありませんので、削除してもＯＫです。
（正常な動作には必須ではありませんが、正確なデータ範囲を取得する意味では補正した方が良いかもしれません。）

空白行が出来る可能性のあるデータの対応

	並び替え列追加法	Collection法
UsedRangeを使用したデータ範囲取得	必須	必須
データ範囲のデータ行数補正	必須	無くても正常に動く

図６－６

１７１行目では、Collectionオブジェクトとして「UniRange」を生成しています。
１７４行目では「CollectionRead」プロシージャを呼出し、１つずつデータを読み取り、既存のCollectionデータと比較しながら並び順を決めて保管していきます。
１７５行目では「CollectionWrite」プロシージャを呼出し、保管したCollectionデータをワークシートに貼り付けていきます。

１７５行目ではワークシート上の値を変更していますので、画面のチラツキ防止と処理速度向上のために、１７３行目で画面更新停止（Application.ScreenUpdating = False）を行っています。

６－３．Collection値の読み取りと並べ替え

図６－３の１７４行目から呼び出されるのが図６－７です。引数として受け取る値は、以下の４つです。
　(1)DataRange ：データ範囲
　(2)DataStep　：１データの行数
　(3)keyRow　　：並び替えワードの位置（行方向）
　(4)keyCol　　：並び替えワードの位置（列方向）

181. '==========　⇩(18)　Collection値の読み取りと並べ替え　================
182. Private Sub CollectionRead(DataRange As Range, DataStep As Long, keyRow As Long, keyCol As Long)
183. 　Dim arrayRange As Variant　　 '1データごとの配列
184. 　Dim i As Long　　　　　　　　　 'カウンタ変数（データの数）
185. 　Dim j As Long　　　　　　　　　 'カウンタ変数（Collection値の数）
186. 　Dim k As Long　　　　　　　　　 'カウンタ変数（データの順番）
188. 　For i = 1 To DataRange.Rows.Count Step DataStep
189. 　　arrayRange = DataRange.Rows(i).Resize(DataStep)
190. 　　k = 0
191. 　　For j = UniRange.Count To 1 Step -1
192. 　　　If UniRange(j)(keyRow, keyCol) > arrayRange(keyRow, keyCol) Then k = j
193. 　　Next j
195. 　　If k = 0 Then
196. 　　　UniRange.Add Item:=arrayRange
197. 　　Else
198. 　　　UniRange.Add Item:=arrayRange, before:=k
199. 　　End If
200. 　Next i
201. End Sub

図６－７

言葉だけでの説明では分かり難いと思いますので、図を交えながら１８８～２００行目の動きを見てみます。
まず１番目のデータの時は、図６－８のようになります。

図６－８

まず１８８行目のFor～Nextはｉ＝１からスタートします。
１８９行目は、データ範囲の１行目（DataRange.Rows(i)）を行方向に１データの行数分だけ拡張（.Resize(DataStep)）した範囲（青い枠の部分）の値を「配列arrayRange」に代入します。
１９０行目はデータ順序のカウンタ変数を初期化（＝０）します。
１９１行目の「UniRange.Count」はCollectionのデータ数ですが、まだ作られていないので「UniRange.Count＝０」です。そうなると１９１行目のFor文は「For j = 0 To 1 Step -1」となり「Step -1 なので、Jの開始値と終了値が成立しない」ために１９２行目を実行しないままFor～Next文を終わります。
この時点でK値は変更されていないため K=0 なので、１９５行目のIf文が成立し１９６行目を実行します。
１９６行目は、Collection値として「配列arrayRange」を登録します。
Collection値として登録されたデータは１番目のデータですので、インデックス「１」で管理されます。

次に２番目のデータの時は、図６－９のようになります。

図６－９

まず１８８行目のFor～Nextは「Step DataStep（今回の場合はDataStep=2）」ですのでｉ＝３です。
１８９行目は、データ範囲の３行目（DataRange.Rows(i)）を行方向に１データの行数分だけ拡張（.Resize(DataStep)）した範囲（青い枠の部分）の値を「配列arrayRange」に代入します。
１９０行目はデータ順序のカウンタ変数を初期化（＝０）します。
１９１行目の「UniRange.Count」はCollectionのデータ数で、１つ作られていますので「UniRange.Count＝１」です。
そうなると１９１行目のFor文は「For j = 1 To 1 Step -1」となりますので、ｊ＝１として１９２行目を実行します。
１９２行目は、If文で「新規に取り込んだ配列の並び替えワード」と「既存Collectionデータの並び替えワード」を比較しています。もし配列側のワードが小い（＝If文が成立）時は、「ｋ＝ｊ」を実行します。
図６－９では「みずたに」と「すぎした」を比較し、「みずたに」＞「すぎした」ですのでIf文が成立し「ｋ＝１」となります。
次に１９５行目のIf文に入りますが、ｋ＝１なので１９８行目を実行します。１９８行目は「ｋ番目のデータの前に新しいデータを追加する」という意味ですので、「１番目のデータの前」つまり「新規に取り込んだ配列arrayRange」が１番目のCollection値として登録され、既存のデータは１番目から２番目に変更されます。

もう少しデータの振り分けが進んだ時点の状況を別な角度から見てみます。
図６－１０は、既存のCollectionデータが３つあり、「新規に取り込んだ配列」の並び替えワードが「そりまち･･･」である状態です。

図６－１０

１９１行目のFor～Next文は「Step -1 」となっていますので既存Collectionデータを下の方から調べて行くことになります。
３番目は「みずたに･･･」ですので１９２行目のIf文は成立し「ｋ＝３」となります。続く２番目、１番目との比較は成立しませんので、１９１～１９３行目のFor～Nextを抜けた時には「ｋ＝３」となっています。
つぎに１９５～１９９行目のIf文に入ると、ｋ＝３ですので１９８行目を実行することになります。実行文は「UniRange.Add Item:=arrayRange, before:=k」ですので、「３番目のデータの前に、Collectionデータとして新規配列データを挿入する」ことになります。
すると図６－１０の右下のように、並び替えワード「そりまち･･･」の配列データは３番目に入り、従来３番目だった「みずたに･･･」のデータは３番から４番のインデックスに変更になります。

図６－１０では新規取得データが「そりまち･･･」でしたが、「わたなべ･･･」だった時の流れを図６－１１に示しました。

図６－１１

１９１行目のFor～Next文で、既存のCollectionデータと並び替えワードを比較していくのですが、どのワードよりも大きいので１９２行目のIf文が成立しません。ということは１９０行目でｋ値を初期化（ｋ＝０）した状態のままですので、１９１～１９３行目のFor～Nextを抜けた時には「ｋ＝０」となっています。
つぎに１９５～１９９行目のIf文に入ると、ｋ＝０ですので１９６行目を実行することになります。実行文は「UniRange.Add Item:=arrayRange」ですので、「Collectionデータとして新規配列データを挿入する」ことになります。挿入する場所を指定しないので、末尾に追加されることになります。
すると図６－１０の右下のように、並び替えワード「わたなべ･･･」は４番目になり、従来データのインデックスの順番には変更ありません。

このようにして、「並べ替えワード」順にデータが並んだ状態でCollectionデータが保管されることになります。

６－４．Collection値の貼付け

図６－３の１７５行目から呼び出されるのが図６－１２です。引数として受け取る値は、以下の３つです。
　(1)DataRange ：データ範囲
　(2)DataStep　：１データの行数
　(3)ansUF1　　：並び替え順序（１＝昇順、２＝降順）

202. '==========　⇩(19)　Collection値のワークシートへの貼付け　================
203. Private Sub CollectionWrite(DataRange As Range, DataStep As Long, ansUF1 As Long)
204. 　Dim i As Long　　 'カウンタ変数（貼付け行位置）
205. 　Dim j As Long　　 'カウンタ変数（Collectionデータのインデックス）
207. 　If ansUF1 = 1 Then　　　 '昇順
208. 　　j = 1
209. 　　For i = 1 To DataRange.Rows.Count Step DataStep
210. 　　　DataRange.Rows(i).Resize(DataStep) = UniRange(j)
211. 　　　j = j + 1
212. 　　Next i
213. 　Else　　　　　　　　　　　 '降順
214. 　　j = UniRange.Count
215. 　　For i = 1 To DataRange.Rows.Count Step DataStep
216. 　　　DataRange.Rows(i).Resize(DataStep) = UniRange(j)
217. 　　　j = j - 1
218. 　　Next i
219. 　End If
220. End Sub

図６－１２

このプロシージャは、保管しているCollectionデータをワークシートに「順番に書き出す」のが目的です。ですのでユーザーが指定した順番（昇順・降順）で場合分けをしています。
（本当はIF文ではなく、「昇順＝１、降順＝２」の値を使って正逆同じコードを考えましたが、上手く思いつきませんでした）
引数として得た「ユーザー指定の順番 ansUF1」の値で、２０７行目で場合分けをしています。
昇順（ansUF1＝１）であれば２０８～２１２行目を実行し、降順（ansUF1＝２）であれば２１４～２１８行目を実行します。

保管しているCollectionデータは図６－７で昇順に既に並べられていますので、昇順の場合は「Collectionデータの並び順で出力」すれば良いことになります。
カウンタ変数ｊは「Collectionデータのインデックス」を表すものですが、昇順ですので２０８行目で「ｊ＝１」と１番目データからを指定し、２１１行目で１つ１つ後ろの方にポインターを動かしていきます。
また、２０９行目のFor～Nextで使用しているカウンタ変数 ｉ は「貼付け場所の行位置」を表しており、各データの先頭行を指定するように「Step DataStep」で間隔を空けています。
２１０行目では左辺に貼付け場所を各データ先頭行に対して「Resize(DataStep)」で拡張し、Collectionデータを貼り付けています。

一方降順の場合は、２１４行目で「j = UniRange.Count」とCollectionデータの最後尾データを指定し、２１７行目で「j = j - 1」と「下のデータから上のデータへ」移動させています。
２１６行目の貼付け位置については２１０行目と同じ式です。

６－５．データ範囲取得

データ取得のコードは「並び替え列追加法」と全く同じです。
コードは載せますが説明については「通常のデータ範囲取得」を参照下さい。

221. '==========　⇩(20)　データ範囲取得１　================
222. Private Function DataArea() As Range
223. 　Dim AllArea As Range　　　 'CurrentRegionで取得される「タイトル＋データの範囲」
225. 　Set AllArea = Cells(titleEndRow, titleStartCol).CurrentRegion
226. 　Set DataArea = Range(Cells(titleEndRow + 1, titleStartCol), Cells(AllArea(AllArea.Count).Row, titleEndCol))
228. 　If AllArea(AllArea.Count).Row = titleEndRow Then
229. 　　Set DataArea = Nothing
230. 　End If
231. 　Set AllArea = Nothing
232. End Function

図６－１３

空白行の可能性があるデータ範囲を取得するコードについても、「並び替え列追加法」と全く同じです。
説明については「データ範囲取得（データ範囲に空白行がある場合）」を参照下さい。

233. '==========　⇩(21))　データ範囲取得２　================
234. Private Function DataArea2() As Range
235. 　Dim EndRow As Long　　　　 '使用セル範囲の最下行位置
236. 　Dim EndLine As Range　　　 '使用セル範囲の最下行範囲
238. 　EndRow = UsedRange.Row + UsedRange.Rows.Count - 1
239. 　Set EndLine = Range(Cells(EndRow, titleStartCol), Cells(EndRow, titleEndCol))
241. 　Do While Application.WorksheetFunction.CountA(EndLine) = 0
242. 　　Set EndLine = EndLine.Offset(-1, 0)
243. 　　DoEvents: DoEvents
244. 　Loop
246. 　If EndLine.Row = titleEndRow Then
247. 　　Set DataArea2 = Nothing
248. 　Else
249. 　　Set DataArea2 = Range(Cells(titleEndRow + 1, titleStartCol), Cells(EndLine.Row, titleendtcol))
250. 　End If
251. 　Set EndLine = Nothing
252. End Function

図６－１４

７．最後に

複数行１データの形式での並び替え方法について紹介しましたが、処理速度については正直あまり考えていません。
並び替え列追加法では１セルずつデータを埋め、またCollection法でもワークシートからの１データずつの読み取りと、１データずつの貼付けを行っているので、大量のデータの場合にはかなり時間が掛かるかもしれませんがご了承下さい。

複数行１データは今回のような手法での並び替えは可能なものの、データ処理には「百害あって一利無し」の並べ方ですので、出来るだけ早く１行１データの状態に改修することをお勧めします。
データを改修する手段として「1行1データの表を複数行1データとして印刷する」の考え方も使えるかもしれません。参考にして下さい。


複数行１データの並び替え（it-043.xlsm）

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始） 解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。