数値に見えるものはすべて数値として並べ替えを行う

エクセル2013 マクロの自動記録で
下記のようになりました。
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("N2:N31" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("J2:J31" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("O2:O31" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers

With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:P31")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

自動記録なので、DataOption:= xlSortTextAsNumbers　の部分も
思うように並べ替えが出来ました。
それを下記のように書き換えたのですが、
Key3のO列が 数値に見えるものはすべて数値として並べ替えを行いません。
どこが違うのでしょうか?また、そもそも全てが違うのでしょうか?
ご教示ください。

最終行 = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:P" & 最終行).Sort _
Key1:=Columns("N"), order1:=xlAscending, _
Key2:=Columns("J"), order2:=xlAscending, _
Key3:=Columns("O"), order3:=xlAscending, _
Header:=xlYes, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortTextAsNumbers

A～P列の1行目は見出しタイトル。
最終行はA列で判断してN、J、O列のレベルで並べ替えを行う。
ただし、O列のみ 「数値に見えるものはすべて数値として並べ替えを行う」

質問者からの補足コメント

• いつもありがとうございます。
  確認ですが
  ・・・・・・・・・
  　　　　 .SortFields.Add Key:=Range("O2:O31"), Order:=xlAscending, DataOption:=xlSortTextAsNumbers
  　　　　 .SetRange Range("A1").CurrentRegion　'これは便宜的に書いた範囲
  
  マクロを読み解く力が無いため確認させてください。
  31行目(O31)としていますが最終行はA列で判断しているということで宜しいでしょうか?
  もしそうだとすると　O2:O100でも構わないのでしょうか?
  （勿論　NもJも　N2:N100やJ2:J100とします)
  
  念の為、教えて頂いたマクロで動作確認して、思い通り動いていますが、
  100までとしても構わないかの確認です。

  No.1の回答に寄せられた補足コメントです。 補足日時：2015/02/14 22:43

No.2 ベストアンサー 回答者： WindFaller 回答日時： 2015/02/15 10:50

こんにちは。

補足でのご指摘ありがとうございます。

>31行目(O31)としていますが最終行はA列で判断しているということで宜しいでしょうか?
気にしている部分でしたので、この部分にやっぱり目が止まりましたか。
最終行は、A列で判断しているのではなく、CurrentRegionの範囲の最終行です。
以下でいうなら、右端下の場所の行です。

Sub TestFindLastRow()
　With Range("A1").CurrentRegion
　　 Debug.Print "左端上: " & .Cells(1).Address(0, 0); " 左端下: " & .Cells(.Rows.Count, 1).Address(0, 0)
　　 Debug.Print "右端上: " & .Cells(1, .Columns.Count).Address(0, 0); " 右端下: " & .Cells(.Cells.Count).Address(0, 0)
　End With
End Sub

実際「範囲内ならどこでもよい式」をコードで表すと、CurrentRegionだったわけです。
上のコードで、Range("A1").の所をRange("D12")にしても、その範囲は変わりません。

連続的に続く範囲を得るのは、CurrentRegionで十分で、その最終行を取っているわけです。

左端－－－－右端
|　　　　　　|
最下行－－－●　.Cells(Cells.Count)
(一見、左端の最下行は、A列のように思えますが、この四角形全体のデータの一番下の行ですから、右端下で最下行を取っています）

単独で使う時には、CurrentRegion にしろ、似たような働きをする UsedRange は、点と点を結んで、四角形に範囲を取得しますが、こちらは連続した面ではなく、点と点を結びつけます。

今見ると、私の昨日のコードは、細かい時点で、あやふやな点が見れます。

ソートやオートフィルタなど範囲を取得する場合には、おそらく、組み込み型のマクロの内部で、
CurrentRegionが働いているらしいと予想をつけて、あえて、こちらから使っているわけなのです。
本来は、ユーザーが明示的に範囲を取得したほうが、分かりやすいコードだと思います。

>31行目(O31)としていますが最終行はA列で判断しているということで宜しいでしょうか?
>もしそうだとすると　O2:O100でも構わないのでしょうか?

おっしゃっている意味はよく分かります。
試してみましたが、結果的には、O2:O100としても、空白が入り込まないようですね。
単に読みやすさ(readability　=可読性)だけの問題で、コードを論理で分かるようにすればいのではないでしょうか？100行まで取ったかの経緯を覚えていればよいのですが、しばらくすると、自分でも、そんな経緯は忘れています。

ご質問で書かれていた
Columns("N")
のような書き方も、本来、全体の論理的な範囲(1行～2^20行）を取るだけで、実際は、データのある物理的な範囲（実際の最下行）までしか届いていないはずです。命令があって、初めて、論理的な最終行(2^20行目）までコードを走らされることになると思います。（この説明ではちょっと分かりにくいと思います）

いずれにしてろ、最終行とCurrentRegionの内容については、誤動作するかしないかは別としても、私のコードは後先が論理的矛盾をしています。便宜的に使ったとはいえ、ご指摘の通り、私のコードは間違っています。
大変失礼しました。

それらの昨日のコードを元に考えれば、こういうコードが書けます。
あくまでも、参考程度で、私はまだ、Sortオブジェクトのコーディング・スタイルを決めているわけではありません。なお、変数名St は芳しくはないのですが、見やすさで、短くすることだけを考えて付けました。

以上、文章がわかりにくかったとは思いますが、分かりにくい部分は、またご指摘ください。

'//
Sub TestSort3()
Dim St As Range
With ActiveSheet
　 Set St = .Range("A1").CurrentRegion 'あくまでも、CurrentRegionにこだわった場合。
　 With .Sort
　　　　 .SortFields.Clear
　　　　　 '※St.Columns("N")は、CurrentRegionの最終行までしか範囲を取りません。
　　　　 .SortFields.Add Key:=St.Columns("N"), Order:=xlAscending, DataOption:=xlSortNomal
　　　　 .SortFields.Add Key:=St.Columns("J"), Order:=xlAscending, DataOption:=xlSortNomal
　　　　 .SortFields.Add Key:=St.Columns("O"), Order:=xlAscending, DataOption:=xlSortTextAsNumbers
　　　　 .SetRange St
　　　　 .Orientation = xlTopToBottom
　　　　 .Header = xlYes
　　　　 .SortMethod = xlStroke
　　　　 .Apply
　 End With
　End With
End Sub
'//

この回答へのお礼

指摘のつもりではありませんでしたが
結果、双方に得たものがあったみたいで
今回も素晴らしい解説と回答をありがとうございました。
実際に教えて頂いたコードで動作確認も出来ました。
ありがとうございました。

お礼日時：2015/02/15 17:47

No.1 回答者： WindFaller 回答日時： 2015/02/14 21:15

こんばんは。

>どこが違うのでしょうか?また、そもそも全てが違うのでしょうか?
どうやら、すべてが違うようですね。
いやはや、私も知らなかったです。（^^;

ヘルプにこのようにかかれています。
名前も、[Sort オブジェクト]となっています。
>バージョン情報
> 追加バージョン: Excel 2007
私の持っている Excel VBAのポケットリファレンス(2003まで）にも載っていません。
ポケリファは便利なので、新しいのを買わないといけないようです。

私たちが慣れ親しんできたのは、旧バージヨンのSortメソッドのようです。
>最終行 = Range("A" & Rows.Count).End(xlUp).Row
>Range("A1:P" & 最終行).Sort _

これでは、少しも並べ替えしてくれそうになかったです。まだ、使えそうなのですが、メソッド側は、ヘルプにも出てこないようです。

以下が正しい書き方かは分かりませんが、これは試行錯誤で得たコードです。
'//
Sub TestSort2()
　 With ActiveSheet.Sort
　　　　 .SortFields.Clear
　　　　 .SortFields.Add Key:=Range("N2:N31"), Order:=xlAscending, DataOption:=xlSortNomal
　　　　 .SortFields.Add Key:=Range("J2:J31"), Order:=xlAscending, DataOption:=xlSortNomal
　　　　 .SortFields.Add Key:=Range("O2:O31"), Order:=xlAscending, DataOption:=xlSortTextAsNumbers
　　　　 .SetRange Range("A1").CurrentRegion　'これは便宜的に書いた範囲
　　　　 .Orientation = xlTopToBottom
　　　　 .Header = xlYes
　　　　 .SortMethod = xlStroke
　　　　 .Apply
　 End With
End Sub
'//