並べ替えCustomOrderの文字列要素数の上限（Excel2013)

Question

SortFieldオブジェクトの引数CustomOrder:　には、設定できる文字列要素数に上限があるのではないかと思い、質問させていただきます

データベース用シートの情報を購読紙のマスタ表に登録してある並びに並べ替えたく、SortFieldオブジェクトの引数のCustomOrder にセル範囲の情報を設定しています

以下のコードで、購読紙のリストシートに180行分、データが登録されていて、それを変数に読み込みCustomOrderに設定しています

購読紙リストのデータ範囲を以下のように取得しています

Set myRngM = .Range(.Cells(2, ColMeigaraRyaku), .Cells(40, ColMeigaraRyaku)) 

この中の末尾の行番号 40　を　maxrow（データ最終行）　に変えると、対象の読み込み数が180個になります

この状態で実行しても並び替えには成功するのですが、ブックを保存&閉じた後に再度開くと以下のエラーメッセージが出ます

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

【ブック名】の一部の内容に問題が見つかりました。可能な限り内容を回復しますか？ブックの発行元が信頼できる場合は、〔はい〕をクリックしてください。

読み取れなかった内容を修復または削除することにより、ファイルを開くことができました。

削除されたレコード: /xl/worksheets/sheet1.xml パーツ内の並べ替え

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

いろいろ、からかった結果、CustomOrderの要素数に上限があるのではないかと推測し試してみたところ、

対象のデータ数が、

・48個までは成功し（並べ替えも成功し、ブック再起動後もエラー表示が出ない）

・それ以降は上記のエラーがでます

・コードに問題がるのか、CustomOrder:に直接文字列をひとつずつ設定してもやはり上限があるようです

（記録をとり忘れてしまい、正確な数ではありませんが、60個くらいでした）

【Q】

これは、Excelの仕様ということなのでしょうか？

それともコードに問題があるのでしょうか？

また、仕様という場合、購読紙のリストシート180行分 の並び通りにしたい場合、代替策のアイデアがあればご教授いただきたいです

===============

使用環境：

・Windows8.1

・intel Corei7/メモリ8GB

・Excel2013

===============

よろしくお願い致します

以下、サンプルコードです

//////////////////////////////////

Option Explicit

Sub Sort_Meigararyaku()

Dim myRng, myRngM As Range

Dim FoundMeigara, FoundDokuNo, FoundMeigaraRyaku As Range

Dim ColMeigara, ColDokuNo, ColMeigaraRyaku As Integer

Dim maxrow, maxclm, maxrowM, maxclmM As Long

Set WsFBM = ThisWorkbook.Worksheets("購読紙CD")

Set WsT = ThisWorkbook.Worksheets("test")

　 '▼購読紙のリストシート

    With WsFBM  

        maxrowM = .Cells(Rows.Count, 1).End(xlUp).Row            '---最終行番号の取得

        maxclmM = .Cells(1, Columns.Count).End(xlToLeft).Column 　'---最終列番号の取得

        '---銘柄略称列の列番号取得

        Set FoundMeigaraRyaku = .Range(.Cells(1, 1), .Cells(1, maxclmM)).Find("銘柄略称", LookAt:=xlWhole)

            ColMeigaraRyaku = FoundMeigaraRyaku.Column

        '---【！】CustomOrder　内の要素数の対象範囲を変数に取得

        Set myRngM = .Range(.Cells(2, ColMeigaraRyaku), .Cells(41, ColMeigaraRyaku))        

    End With

 　'▼データベースシート

    With ThisWorkbook.Worksheets("test")    

        maxrow = .Cells(Rows.Count, 1).End(xlUp).Row

        maxclm = .Cells(1, Columns.Count).End(xlToLeft).Column

        Set myRng = .Range("A1").CurrentRegion　'---並べ替え対象のデータ範囲を変数に取得

'---銘柄略称列の列番号取得

        Set FoundMeigara = .Range(.Cells(1, 1), .Cells(1, maxclm)).Find("銘柄", LookAt:=xlWhole)

            ColMeigara = FoundMeigara.Column

        '---顧客番号列の列番号取得

        Set FoundDokuNo = .Range(.Cells(1, 1), .Cells(1, maxclm)).Find("読者番号", LookAt:=xlWhole)

            ColDokuNo = FoundDokuNo.Column

   '▼並べ替え処理

        .Sort.SortFields.Clear

        '◆キー１（読者番号）

        .Sort.SortFields.Add Key:=.Columns(ColDokuNo), _

              SortOn:=xlSortOnValues, _

              Order:=xlAscending, _

              DataOption:=xlSortNormal

        '◆キー２（購読紙略称）

        .Sort.SortFields.Add Key:=.Columns(ColMeigara), _

              SortOn:=xlSortOnValues, _

              Order:=xlAscending, _

              CustomOrder:="""," & Join(Application.Transpose(myRngM), ",") & """", _

              DataOption:=xlSortNormal

        With .Sort

            .SetRange myRng

            .Header = xlYes

            .MatchCase = False

            .Orientation = xlTopToBottom

            .SortMethod = xlPinYin

            .Apply

        End With

    End With

End Sub

Answer 1

参考になるかどうかわかりませんが、

http://oricyo.hida-ch.com/e592066.html

「エクセル２０１０でのユーザー設定リスト２５５文字制限の解決法」

ここに

「H26　3追記

  気になったので後日サポ-トに問い合わせたところ

  エクセル２０１０・エクセル２０１３では最大２５４行までしか入らないようです。　残念・・」

というのがありました。

Answer 2

代替案としては以下を思いつきましたが、CustomOrderの上限に関しての情報や、代替のアイデアがあればご教授いただきたいです

以下、代替案です

===================

並べ替えの条件として、

優先キー：読者番号（数値）

次キー　：略称銘柄(文字列)　【⇒今引っかかっている部分 CustomOrderの上限】

とした場合、

1. 購読紙リスト内に作業列を設け、順次、現在の行番号を取得し作業用セルに代入。その作業列を「並び順」とする（見出し）
2. データベース内にも「並び順」列を作成し、そこに略称銘柄列をキーに購読紙リストの「並び順」番号を表引きする
3. データベース内で、優先キー：読者番号／次キー：並び順　とすることで意図した並べ替えが実現できそう

Answer 3

>TETUO  さま

ご返信ありがとうございます

>　http://oricyo.hida-ch.com/e592066.html

> 「エクセル２０１０でのユーザー設定リスト２５５文字制限の解決法」

おそらく、これと関連しているのではないかと思います（良記事をありがとうございます）

ご紹介いただいたTips内の下記過程をマクロ記録でとってみて応用できるか試しましたが、厳しそうです

==================

ファイル

↓

下から２番目のオプション

↓

ウインドウが出るので「詳細設定」

↓

下にスクロールしていくと「ユーザー設定リストの編集」ボタンがありますのでクリックすると見慣れたウインドウが出ます。

↓

ここで、「リストの項目」にコピペするのではなく、あらかじめワークシートにリストを貼り付けてマウスで範囲指定するか、インポートボタンの左にあるボタンを押し範囲設定します。

↓

インポートボタンを押します。

==================

これをマクロ記録をしながら行いましたが、ブック再起動時にやはりエラーが出てしまいました

対象レコード172／約900文字

得られた文字列情報を、CustomOrderにセットする形は変わらないということですよね

※今回は、Application.AddCustomList ListArray:=Range("D2:D173") という形で取得

Tips内(Excel2010)だと、

---

＊全角１９００文字程度（画面では９１行）までインポート

＊文字数を減らしてテストしたら最大２５４行までインポート可能でした。

---

でしたが、

私の環境(Excel2013)では、

---

・全角206文字

・48行　までインポート可能でした

---

40行以降の項目についてはランダムな並びになってしまう、ということでユーザーに周知するしかないのかなと考え始めています・・・

Answer 4

>BANSUI さま

ご返信ありがとうございます

Excel の仕様と制限

を確認しましたが、並べ替えに関しては

======================================

並べ替えに使用するキー

⇒1 回の並べ替えでは 64、連続して並べ替えるときは無制限

======================================

という記述はありますが、

CustomOrderの文字列要素数に関しての記述は見当たりませんでした

Answer 5

**Excel の仕様と制限**ではどうですか。