[[20230111171614]]　『VBAでのソート』（こりき）　ページの最後に飛ぶ

『VBAでのソート』（こりき）

お世話になります。

毎週一回提出する資料の加工用にVBAを書いています。
肝心のソート部分がうまくいかないので質問させていただきます。

【加工対象のExcelデータについて】
１．1ブック2シートのみ(シートが増えることはありません)
２．AA列からJ列までデータあり、開始はセルA4です。1～3行目もデータは入っていますがソートに関係ありません
３．週ごとに行数が増減します
４．4行目はタイトルで、ソートは最優先がE列昇順、次がB列昇順です。ソート条件はこの二つです

【書いたコード】
(↓これの上に他にコード書いてあります。ここまでエラーはありません)

ActiveWorkbook.Worksheets("Report").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Report").Sort.SortFields.Add Key:=Range("E5").CurrentRegion _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Report").Sort.SortFields.Add Key:=Range("B5").CurrentRegion _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Report").Sort
        .SetRange Range("A4").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

【困っていること】
コードしたから2行目の.Applyでエラーが発生しています。
ネットや書籍で調べたのですが、自分では何が間違っているのかわからないので、こちらで質問させていただきます。
間違いの指摘と解決方法のご教授をよろしくお願いします。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

ばんわ～＾＾
チラ見なので、外しているかもですが。。。
私もよくやらかしますので。
.CurrentRegion　。。。←　プンプン臭います。。＾＾；
ご確認[アドレス]を
m(__)m
（隠居Z） 2023/01/11(水) 18:24:44

既に指摘されていることに加えて、シートの指定に問題あったりしませんか？

   Sub 抜粋()
      With ActiveWorkbook.Worksheets("Report")
         .Sort.SortFields.Clear
         .Sort.SortFields.Add Key:=.Range("E5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
         .Sort.SortFields.Add Key:=.Range("B5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

        .Sort.SetRange .Range("A4").CurrentRegion
        .Sort.Header = xlYes
        .Sort.MatchCase = False
        .Sort.Orientation = xlTopToBottom
        .Sort.SortMethod = xlPinYin
        .Sort.Apply
      End With
   End Sub

（もこな２） 2023/01/11(水) 19:59:08

隠居Z　様

おはようございます。
お返事ありがとうございます。

.CurrentRegionのアドレスを調べる、ということですね。

このマクロを書くにあたって、週ごとに行数の増減があるので
どこからどこまでというセルの範囲指定ができないことからCurrentRegionにたどり着きまして
ネットや参考書を読んだ限りではこれでよさそうな気がしていたのですが。

.CurrentRegionのアドレスということがまだよくわかってないかもしれませんので、もう一度調べ直します。またご報告いたします。

（こりき） 2023/01/12(木) 10:25:03

もこな２　様

おはようございます。
お返事ありがとうございます。

コードありがとうございました！

こちらで動かしてみたところ、並び替えはできましたが、2行目からソートが始まりました。
4行目以降データ範囲をソートしたいのですが、私が書いたコードもやっぱり2行目からソートされてしまって解決方法がよくわからない状況です。

シートですが、「Pivot Table」「Report」という2シートがデフォルトで抽出されまして(弊社システムのフォーマットです)、「Report」に並び替えをしたいデータが入っているのでおそらくシートの指定についてはこれで合っているのではないかと思います。

もう少し調べて、またご報告いたします。

（こりき） 2023/01/12(木) 10:32:50

おはようございます。
キーは一つのセル指定で良いみたいなので、もこな2さんのご案内で
解決かと。＾＾ｖ
残るは
.Sort.SetRange .Range("A4").CurrentRegion
かと
Intersect
というのが御座いますので、範囲の重なり部分を抽出出来ますので
先頭行が4行目固定なら最終行をを調べて、ROWSで、4行目～最終行[範囲1]
と↑のカレントレジオン[範囲2]:をパラメーターに指定してあげると
切り出せると思いますです。←私はよく使いますです。＾＾；
m(__)m
（隠居Z） 2023/01/12(木) 10:45:56

隠居Z　様

お返事ありがとうございます！

Intersectというのが御座いますので

ヒントありがとうございます。
ずっとIntesectについて調べていましたが、まだよくわからないので調査継続中です。
わかり次第またご報告いたします。
（こりき） 2023/01/12(木) 12:52:36

もこな2　様

先程はコードありがとうございました。

ところで

.Sort.SetRange .Range("A4").CurrentRegion

ですがよく見たら、「.Sort.SetRange」と「.Range("A4").CurrentRegion」の間に半角スペースがあることにきづき、これを詰めてみたところエラーが発生し、元に戻したところソートができました(ただし2行目以降ですが)

この半角スペースには何か意味があるのでしょうか。
すみませんが教えてください、よろしくお願いします。
（こりき） 2023/01/12(木) 12:53:53

＞＞この半角スペースには何か意味があるのでしょうか。
メソッドの引数だと思いますよ。
ご自分で、パラメーターを持つプロシジャーを作成して見れば
なぁ～ンとなく解かるのではないでせうか。
メソッド
プロパティ
詳細は↑等々でご検索いただくと解説サイトがたくさん。有りますよ。
大雑把な説明で相済みません、諸先輩の皆様。また、補足等々、御座
いましたら、宜しく。
m(__)m
（隠居Z） 2023/01/12(木) 15:02:22

 Option Explicit
Sub b()
    Dim wb, rr, lr&
    Set wb = ThisWorkbook
    With wb.Worksheets("Report")
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        lr = IIf(lr < 4, 4, lr)
        If lr < 5 Then Exit Sub
        Set rr = Intersect(.Range("A4").CurrentRegion, .Range(.Rows(4), .Rows(lr)))
        With .Sort
            .SortFields.Clear
            .SortFields.Add Range("E5")
            .SortFields.Add Range("B5")
            .SetRange rr
            .Header = xlYes
            .Apply
        End With
    End With
End Sub
こんな感じでせうか。。。＾＾；
m(__)m
（隠居Z） 2023/01/12(木) 16:22:32

■１
＞おそらくシートの指定についてはこれで合っているのではないかと思います。
コードを標準モジュールに書いていることが前提になりますが、シートの指定を省略した場合、ActiveSheetを指定したことになります。
なので、最初のコードを整理するとこうなります。

   Sub 抜粋()
      With ActiveWorkbook.Worksheets("Report")
         .Sort.SortFields.Clear
         .Sort.SortFields.Add Key:=ActiveSheet.Range("E5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal '★ActiveSheetに依存
         .Sort.SortFields.Add Key:=ActiveSheet.Range("B5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal '★ActiveSheetに依存
         .Sort.SetRange ActiveSheet.Range("A4").CurrentRegion '★ActiveSheetに依存
         .Sort.Header = xlYes
         .Sort.MatchCase = False
         .Sort.Orientation = xlTopToBottom
         .Sort.SortMethod = xlPinYin
         .Sort.Apply
      End With
   End Sub

すわなち「ActiveWorkbook.Worksheets("Report")」と「ActiveSheet」が同じシートを示しているのですか？というのが私のコメントの趣旨です。

■２
＞4行目以降データ範囲をソートしたい
既にアドバイスを受けていますが、「.Range("A4").CurrentRegion」がどのセル範囲を示しているのか手作業で確認されてみてはどうですか？
もしくは、難しく考えずに4～適当な行までを範囲にするとか・・・

   Sub 抜粋()
      With ActiveWorkbook.Worksheets("Report")
         .Sort.SortFields.Clear
         .Sort.SortFields.Add Key:=.Range("E5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
         .Sort.SortFields.Add Key:=.Range("B5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
         .Sort.SetRange .Range("A4:E10000")　'★ここを必要であろう行までにする
         .Sort.Header = xlYes
         .Sort.MatchCase = False
         .Sort.Orientation = xlTopToBottom
         .Sort.SortMethod = xlPinYin
         .Sort.Apply
      End With
   End Sub

（もこな２） 2023/01/12(木) 17:55:45

隠居Z　様

おはようございます。
昨日はあれから上司に別途急ぎの用件を依頼されたものでVBAは一旦保留となってしまいました。
お返事ありがとうございました。

＞＞この半角スペースには何か意味があるのでしょうか。
＞メソッドの引数だと思いますよ。
＞ご自分で、パラメーターを持つプロシジャーを作成して見れば
＞なぁ～ンとなく解かるのではないでせうか。

とのこと、教えていただきありがとうございます。
まだ学習中なのでもっと調べてみます。

さて、コードありがとうございました！
実行してみたところ

With wb.Worksheets("Report")
ここで「実行時エラー9　インデックスが有効範囲にありません」が出ました。
確認したのですがワークシート名はこれで間違いないので何が引っかかっているのか謎です。

やりたいことだけ調べてもなかなか答えは出ないものですね。
先はまだ(本当に)長そうですが、学習を継続していきます。

（こりき） 2023/01/13(金) 10:52:55

もこな2　様

おはようございます。
細かいご説明ありがとうございます。
昨日は途中で急ぎの用件を頼まれてしまい、こちらにまで手が回らなくなってしまいました。

ご質問の件ですが、

■1
＞すわなち「ActiveWorkbook.Worksheets("Report")」と「ActiveSheet」が同じシートを示しているのですか？というのが私のコメントの趣旨です。

はい、さようでございます。
このブックにはワークシートが2枚しかなく(DBシステムから抽出しています)、1枚は「Pivot Table」でもう1枚が「Report」です。「Pivot Table」は使う必要が全くないので削除していいと思うのですが、前任者から引き継いだ時そのままなので同じようにしています(削除していいような気がしてきました)。

■２
＞4行目以降データ範囲をソートしたい
既にアドバイスを受けていますが、「.Range("A4").CurrentRegion」がどのセル範囲を示しているのか手作業で確認されてみてはどうですか？

手作業で確認というのは、隠居ZさんのIntersectメソッドのことですよね？
これがまたわからなくて昨日ずっと調べていました(まだわからずじまいです)。

さてコードのご提案ありがとうございました。
上記書いてくださった、行指定を1000までにして動かしたら理想の形になりました！

今のところ1000行までは増えないのでひとまずこちらを採用して運用させていただきます。
初心者ながら、最終行を自動取得するようなコードの方がスマートな書き方かなと思っていましたがまだそんな生意気なことはできませんね。

いろいろご教授いただきましてありがとうございました。
（こりき） 2023/01/13(金) 11:11:08

隠居Z　様
もこな2　様

実は前任者から引き継いだマクロがあったのですが(作成者はまた別のスタッフ)それがある日動かくなりまして、そのメンテナンスもかなり面倒臭かったので諸々簡略化して自分で書くことにしたのです。

動かなくなったマクロはかなり複雑で標準モジュールと、Functionとやらがありこれを改造するのは至難の業なので1から自分で書き直してみました。

Excelの通常のソートは簡単なのにコードでソートを書くのがこんなに大変だとは思ってもみませんでした。
ただ、わからなかったことが少しずつわかってくると楽しいですね。

コメントをお寄せくださり、ありがとうございました。

（こりき） 2023/01/13(金) 11:14:18

 ＞＞＞この半角スペースには何か意味があるのでしょうか。
 ＞＞メソッドの引数だと思いますよ。
 ＞＞ご自分で、パラメーターを持つプロシジャーを作成して見れば
 ＞＞なぁ～ンとなく解かるのではないでせうか。
 ＞とのこと、教えていただきありがとうございます。
 ＞まだ学習中なのでもっと調べてみます。

 半角スペースが引数の訳ないでしょっ。(書き方の問題だとは思いますが）

 .Sort.SetRangeメソッドに引数を与える為に、区切りとしてスペースを入れただけです。

 こう書くのと同じことです。（Callを省略した時は、引数を囲っていたカッコも省略するので、上の書き方になります）
 　↓
 Call .Sort.SetRange(.Range("A4").CurrentRegion)

（半平太） 2023/01/13(金) 11:41:49

半平太　様

おはようございます
コメントありがとうございます。

今Callステートメント調べました、半角スぺ＾スの意味が分かりました。
ありがとうございました。
（こりき） 2023/01/13(金) 12:06:09

隠居Z　様

おはようございます。

質問ですが、上記書いてくださったコードの変数で

＞Dim wb, rr, lr&

wbはworkbookだと思いますがrr, lr&は何を指しているのでしょうか。
お手数をおかけしますが教えてください、よろしくお願いします。

（こりき） 2023/01/13(金) 12:09:22

こんにちわ～＾＾
仰る通りで御座います。
【パラメータの指定方法としてのスペース】と云いたかっただけです。＾＾；
半平太さん
的確なご指摘、並びに、補足、有難う御座いました。
何時も済みません～(*^^*)
こりき　さん　済みませんでした。確かに、惑わせるような回答でした。
気を付けますです。
m(__)mm(__)mm(__)m

＞＞Dim wb, rr, lr&　　　←手抜き工事です済みません
wb バリアント型
rr バリアント型
lr Long型
を略式で宣言しています。
Dim wb As Workbook
Dim rr As Range
Dim lr As Long
と書くべきでした。m(__)m
詳細は
VBA　変数の型。等々でご検索を。m(__)m
＾＾；
m(__)m

（隠居Z） 2023/01/13(金) 12:16:37

＞＞With wb.Worksheets("Report")
＞＞ここで「実行時エラー9　インデックスが有効範囲にありません」が出ました。
は
解決されましたでしょうか。
私のコードではマクロが書かれているブックを格納していますので
処理対象のブックがその他のブックでしたらエラーになると思います。
ActiveWorkbook.Worksheets("Report")
ActiveWorkbook
は場合によっては変動的な部分が有ると思いますので明示的にブック名
等で指定すれば良いかと思います。
m(__)m
（隠居Z） 2023/01/13(金) 12:35:31

隠居Z　様

お返事ありがとうございます。
半角スペースについての説明文をちょうど読み終えたところです(ググってみました)。
こちらこそわからないことだらけで、説明をして下さる皆様にもどかしい思いをさせてしまって申し訳ありません。

変数についてもご説明ありがとうございました。
本当にこんな初歩的なことからまだわかってなかったのかとつくづく思いしりました。
お手数をおかけいたしました、ありがとうございます。
（こりき） 2023/01/13(金) 12:37:45

隠居Z　様

＞＞With wb.Worksheets("Report")
＞＞ここで「実行時エラー9　インデックスが有効範囲にありません」が出ました。

こちらまだでございます。すみません。

質問をする→お返事をいただく→その内容をググって調べる(知らないことが多い)

のループをしてましてまだこちらまでたどり着いておりませんでした。

＞私のコードではマクロが書かれているブックを格納していますので
＞処理対象のブックがその他のブックでしたらエラーになると思います。
承知いたしました！同じ条件でもう一度試してみようと思います。

結果が出ましたらまたご報告させていただきます。
（こりき） 2023/01/13(金) 12:41:11

隠居Z　様

できたー！！
できました！！

先程のコードを加工するブックにに格納したところ、エラーも出ず並び替え完成いたしました。
求めていたシートが出来上がりました！

毎週金曜日に新しくデータを抽出するため、出来ればマクロは別ブックで保存しておきたいのであともう少し調べて理想の結果にできるようにします。

隠居Z様、もこな2様からご提案いただいたコードと
自分が書いたコードを比較して何が間違っていたかの検証もします。

こちらで質問してよかったです。
ありがとうございました。

（こりき） 2023/01/13(金) 12:50:53

書いている間に解決したようですが、コメントしておきます。

■３
＞はい、さようでございます。
誤解されているかもしれないので、ちょっと補足。
【標準モジュール】シートの指定を省略した場合は、ActiveSheetを指定したものとして扱われるというお話をしました。
では【シートモジュール】でシートの指定を省略した場合はどうなるかというと、そのシートを指定したものとして扱われます。

したがって、↓の場合に「ActiveWorkbook.Worksheets("Report")」と「指定が省略されたシート」が一致しなくなります

 【標準モジュール】に記述している場合であって、「Pivot Table」シートがアクティブである場合
 【シートモジュール】に記述している場合

よって「「Pivot Table」は使う必要が全くないので削除していいと思う」とのことですが、そういった解決法よりきちんと（ブックや）シートを指定することを考えた方が確実でしょう。

■４－１
＞手作業で確認というのは、隠居ZさんのIntersectメソッドのことですよね？
まったく違います。
元のコードでは↓のようになっていたわけです。（標準モジュールに書いてあったとして説明してます）

 ActiveSheet.Range("A4").CurrentRegion

すなわち↓で選択されるセル範囲を確認してみては？という意味です。

 (1) A4セルを選択する
 (2) キーボードの「Ctrl」と「*」を同時押しする

おそらく２～３行目も表範囲として認識されているのでしょう。

■４－２
Intersectメソッドについてはちゃんと調べてみましたか？
セル（範囲）が重なった部分を取り出す命令なわけですから、例えば↓のようにすれば４行目以降になるでしょうとアドバイスを受けています。

    Sub 実験()
        Worksheets.Add
        Range("A4:E10").Value = "★"
        Range("B2,C3").Value = "♪"

        MsgBox _
         "Range(""A4"").CurrentRegion" & vbLf & "  " & _
         Range("A4").CurrentRegion.Address(False, False) & "　←２行目からになる" & vbLf & vbLf & _
         "Intersect(Range(""A4"").CurrentRegion,.Rows(""4:"" & Rows.Count))" & vbLf & "  " & _
         Intersect(Range("A4").CurrentRegion, Rows("4:" & Rows.Count)).Address(False, False) & "　←４行目からになる"
    End Sub

（もこな２） 2023/01/13(金) 12:59:11

もこな2　様

こんにちは。
コメントありがとうございます。

■３
＞誤解されているかもしれないので、ちょっと補足。

【シートモジュール】を使ったことがなかったのでこれがどういうものか調べてました。
念のため作ってあるマクロのシートモジュールを見ましたが何も書いていませんでした。
(、と、このお返事も果たして理解したうえで書けているか不安なのですが)

＞よって「「Pivot Table」は使う必要が全くないので削除していいと思う」とのことですが、そういった解決法よりきちんと（ブックや）シートを指定することを考えた方が確実でしょう。

ご指摘ありがとうございます。
あまり短絡的に考えないようにします。

■４－１
＞元のコードでは↓のようになっていたわけです。（標準モジュールに書いてあったとして説明してます）←はい、そうです。

＞ActiveSheet.Range("A4").CurrentRegion

＞すなわち↓で選択されるセル範囲を確認してみては？という意味です。
＞(1) A4セルを選択する
＞(2) キーボードの「Ctrl」と「*」を同時押しする
＞おそらく２～３行目も表範囲として認識されているのでしょう。

そういうことだったのですね、理解しておらず失礼いたしました。
今確認したところ1行目から最終行までデータが入っているセルが全選択されました。

■４－２
＞Intersectメソッドについてはちゃんと調べてみましたか？

こちらについてはまだ手が回ってないのでこれから調べます。
書籍とネットの情報は読みましたが、まだ自分で試しコードを書いたり出来てないのです。
すみません。

調べてそれを理解するまでにどうしても時間がかかってしまいまして、すぐお返事ができずに申し訳ありません。
（こりき） 2023/01/13(金) 15:44:21

コメント返信：

[ 一覧(最新更新順) ]

[[20230111171614]] 『VBAでのソート』（こりき） ページの最後に飛ぶ

[[20230111171614]]　『VBAでのソート』（こりき）　ページの最後に飛ぶ