オートフィルタで絞り込んだデータだけ特定する

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

オートフィルタで絞り込んだ結果を別のシートなどにコピーするとき、簡単にやるなら次のような感じです。ここでは、次の表を「1列目が"田中"」で絞り込むとします。

Sub Macro1()
    Range("A1").AutoFilter 1, "田中"
    Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")
End Sub

オートフィルタの結果が、たとえ100件であっても、0件であっても、常にタイトル行は表示されています。だから、表示されているセルA1を含む全体(CurrentRegion)をコピーしています。

簡単ですが、もちろんこの考え方ではタイトル行ごとコピーされてしまいます。そりゃそうですね。でも、実務はそんなに簡単ではありません。タイトル行を除いたデータだけコピーしたいこともあります。いや、むしろ多いです。そんなときの考え方です。

まずは、セルA1を含む全体「Range("A1").CurrentRegion」を、1行下にずらしてやります。

ただ、これでは関係のない、表の下の1行が含まれてしまいます。オートフィルタで扱う表では、一般的にここは何も表示されていないはずです。ですから、このままコピーなどしてもいいのですけど、コピー元とコピー先で書式が異なっていたりすると面倒です。

関係のない最下行を削るには、この範囲の大きさを、1行少なくしてやります。使うのはResizeです。現在の範囲は5行です。この範囲を4行にします。

さて、今指定した「4」とは、どういうことでしょうか。これは、最初の範囲の行数(5行)から1を引いた数です。

行数は、Rows.Countで分かりますね。

まとめると、次のようになります。

セルA1を含む全体が「Range("A1").CurrentRegion」です。これを1行下げたのが「Range("A1").CurrentRegion.Offset(1, 0)」です。この範囲を2回使います。

  1. この範囲の大きさを変える → この範囲.Resize
  2. この範囲の行数を調べる → この範囲.Rows.Count

なので、この範囲をオブジェクト変数に入れておくと分かりやすいです。

Sub Macro1()
    Dim この範囲 As Range
    Range("A1").AutoFilter 1, "田中"
    Set この範囲 = Range("A1").CurrentRegion.Offset(1, 0)
    この範囲.Resize(この範囲.Rows.Count - 1).Copy
End Sub

ここでは、理解してもらうために"この範囲"という変数名を使いました。実際には、もっとカッコイイ変数名を使ってください。あるいは、私のようにWith派の方は、次のように書くこともできます。

Sub Macro1()
    Range("A1").AutoFilter 1, "田中"
    With Range("A1").CurrentRegion.Offset(1, 0)
        .Resize(.Rows.Count - 1).Copy
    End With
End Sub

私はこの方がイメージしやすいので、この書き方をしています。