none
.AdvancedFilterのCopyToRange:=の位置指定とは違うところに生成する RRS feed

  • 質問

  • 左上隅B9~右下隅P45にデータがあり、そのD列の出現値から
    重複のないリストをAB12セルから下方向に作りたいと、
    以下のVBAを作って実行しても、AB12ではなく、AE20から下に
    そのリストを生成します。指定の位置を無視します。
    行方向に+8行、列方向に+3列進んだセル位置なので、
    CopyToRange:=.Range("Y4")
    とすると希望のAB12に作ることができます。
    "Y4"はただの逆算でしかありません。

    Sub ボタン①_Click()
    With ActiveSheet
      With .Range("D9:D45")
        .AdvancedFilter Action:=xlFilterCopy, _
                        CriteriaRange:=Range("AB2:AB3"), _
                        CopyToRange:=.Range("AB12"), _
                        Unique:=True
      End With
    End With
    End Sub

    以前にもっと簡単な.AdvancedFilterでうまくできることを
    テストしていたので、元データと生成位置が遠いからかと
    想像し、D列のデータをZ9:Z45に値貼り付けし、希望のAB12
    との間に1列しかない近距離から参照するよう、以下のように
    すると希望のAB12に作ることができます。

    Sub ボタン②_Click()
    With ActiveSheet
       .Range("Z9:Z45").AdvancedFilter Action:=xlFilterCopy, _
                        CriteriaRange:=Range("AB2:AB3"), _
                        CopyToRange:=.Range("AB12"), _
                        Unique:=True
    End With
    End Sub

    どれだけ離れた位置だとしても指定した位置とは違うところに
    作るのは勝手なことをしている、不具合と考えます。
    修正されることを希望します。

    よろしくお願いします。

    2021年3月28日 15:02

すべての返信

  • すみません、Excel 2019 バージョン2102 (ビルド13801.20360 クイック実行)です。
    2021年3月28日 15:05
  • Kuni林さん、今晩は。

      With .Range("D9:D45")

        .AdvancedFilter Action:=xlFilterCopy, _
                        CriteriaRange:=Range("AB2:AB3"), _
                        CopyToRange:=.Range("AB12"), _
                        Unique:=True
      End With

    としているからではないのでしょうか?

    上記の部分を以下の通りに直せばよいだけではないのでしょうか?

    .Range("D9:D45").AdvancedFilter Action:=xlFilterCopy, _

                        CriteriaRange:=Range("AB2:AB3"), _
                        CopyToRange:=.Range("AB12"), _
                        Unique:=True

    2021年3月28日 15:54
  • それはありがちな勘違いかな。

        With Range("B1")
            MsgBox .Range("A1").Address
        End With

    上記を実行すると、表示されるのは「$B$1」です。

    不具合じゃないですよ。

    2021年3月28日 18:37
  • KokemomoYamamomoさん、minmin312さんのご指摘のとおり
    修正すると指定の通り正しく動きます。ありがとうございます。

    Rangeの指定をパラメータで指定するようなときに
    WithでRangeの指定をしてはいけないというようなことですね。

    Msgboxの例では.Range("A1")が全く無視されていますが
    .AdvancedFilterの場合は.Range("D9:D45")と.Range("AB12")が
    混ざってややこしくなるということなんでしょうか。

    Withの使い方が、「単にまとめる」程度だと思っていたので
    Withで囲んだ間は同種のものに「大きく影響を与える」とか
    「無効にする」「つぶす」と考えるべきですか?しくみの理解がイマイチです。

    2021年3月29日 12:45
  •  .Range("D9:D45").Range("AB12")になるのはおかしいというのは気がつきました。
    2021年3月29日 12:52
  • Kuni林さん、今晩は。

    えーと、釈迦に説法の様な気もしますが、次の場合、

    With ActiveSheet
      With .Range("D9:D45")
        .AdvancedFilter Action:=xlFilterCopy, _
                        CriteriaRange:=Range("AB2:AB3"), _
                        CopyToRange:=.Range("AB12"), _
                        Unique:=True
      End With
    End With

    以上のコードをWith を外して書くと、次の様になります。

     ActiveSheet.Range("D9:D45").AdvancedFilter Action:=xlFilterCopy, _                   CriteriaRange:=Range("AB2:AB3"), _
                       CopyToRange:= ActiveSheet.Range("D9:D45").Range("AB12"), _                    Unique:=True

    アンダーラインを引いた部分が2つのWith文により付加されたコードです。要するに、「With ほげほげ」 の後で、「.へげへげ」 と記していると、「へげへげ」 の前の"."の前に 「ほげほげ」 が付いているものとみなして読み込まれる、ということです。

    >Msgboxの例では.Range("A1")が全く無視されています
    無視されていませんよ。これは、
    MsgBox 
    Range("B1").Range("A1").Address とみなされて読み込まれているわけです。
    Range("B1").Range("A1") は、$B$1レンジの中のA1レンジすなわち、$B$1レンジの中の最上最左のレンジということですので、すなわち$B$1のレンジになるわけです。

    例えば、ActiveSheet.Range("A1") は、ActiveSheetの全てのセル範囲の中のA1レンジすなわち最上最左のレンジなので、$A$1のレンジとなりますよね。それと同じで、Range("B1:D1").Range("A1") は、セル範囲$B$1:$D$1の中の最上最左レンジですので、$B$1レンジになるわけです。ここら辺の話は、ネットで検索すればいろいろ見付かると思います。別途、検索してみていただければと思います。

    >Withの使い方が、「単にまとめる」程度だと思っていたのでWithで囲んだ間は同種のものに「大きく影響を与える」とか「無効にする」「つぶす」と考えるべきですか?しくみの理解がイマイチです。
    With は、コードを記入するときに、繰り返し使うコード(オブジェクトを意味するコードとか)を省略して見やすくするとか、楽をする、時に使う、という事でしょうか。残念ながら、人にしっかり正確にご説明するほど、しっかり頭の中で整理されているわけではないので、説明はこれくらいにしておきたいと思います。ネット上でも検索すれば解説記事が見つかると思います。結構便利に使えるテクニックですので、どうぞ、しっかり勉強して正しくご理解いただくのがよろしいかと存じます。

    2021年3月29日 13:09
  • > Msgboxの例では.Range("A1")が全く無視されていますが

    例が、「Range("A1")」なのでわかりづらいのだと思います。

    一般論として、「Range("A1")=Cells(1, 1)」なのはわかりますよね?

    下記のような書き方だと理解しやすくなりませんか?

    With Range("B1:C2")
        MsgBox .Cells(1, 1).Address '<- $B$1 が返ります
    End With

    2021年3月30日 8:41
  • 私のテキトーなWithの使い方が、正確なVBA文法解釈で、
    忠実に実行された結果、期待外れのものになったということが
    よくわかりました。
    KokemomoYamamomoさん、minmin312さん ありがとうございます。

    昔のコンピュータ用語、自分でやってしまったのですね。
    「ガベージイン、ガベージアウト」

    2021年3月30日 12:31