大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、オートフィルタの結果をコピーや削除する方法について、ご紹介します。コピーや削除は「.Copy」と「.Delete」を使います。オートフィルタでフィルタした結果は、SpecialCellsで可視セルを操作する、みたいなことはしなくてもそのまま「コピー」や「削除」ができます。注意点は「フィルタ結果がない」場合には、「実行しない」ように条件を組むことです。

はじめに

この記事では、オートフィルタの結果をコピーや削除する方法について、ご紹介します。

オートフィルタの結果をコピーするには「.Copy」で、削除する場合は「.Delete」を使います。

オートフィルタでフィルタすると、そのまま「コピー」や「削除」をすることができます。

「SpecialCells」で可視セルを選択は必要ないです。

コピーや削除をする際の注意点は、フィルタ結果がない場合は、実行しないようにする、といいうことです。

すべてのデータがコピーや削除されてしまうので、注意です。

では、VBAでオートフィルタの結果をコピーや削除する方法について、解説していきます。

この記事で紹介すること

  • VBAでオートフィルタの結果をコピーや削除する方法

目次から見たい項目へ移動すると便利ですよ。

目次

オートフィルタの結果をコピーする

オートフィルタの結果をコピーしてみます。

結果は表示している値だけをコピーできる

結果は表示している値だけをコピーできます。

  • 「東京」でフィルタ
  • フィルタ結果をセルにコピー
  • オートフィルタを解除

という流れです。

Sub TEST1()
    
    '東京をフィルタ
    Range("A1").AutoFilter 2, "東京"
    
    'フィルタ結果をセルにコピー
    Range("A1").CurrentRegion.Copy Range("E1")
    
    'オートフィルタを解除
    Range("A1").AutoFilter
    
End Sub

表を用意しておきます。

表を用意

表を用意

実行してみます。

「東京」だけをフィルタして「コピー」

「東京」だけをフィルタして「コピー」

「東京」だけをフィルタして「コピー」できました。

簡単です。

結果をコピーモードにして確認してみる

フィルタした結果を「コピーモード」にして確認してみます。

'東京でフィルタ
Range("A1").AutoFilter 2, "東京"

'フィルタ結果をコピー
Range("A1").CurrentRegion.Copy

同じ表を使います。

表を用意

表を用意

実行してみます。

フィルタ結果だけをコピーしてくれる

フィルタ結果だけをコピーしてくれる

点線がとびとびになっていますね。

こんな感じで、フィルタした結果だけをコピーしてくれるので、そのままコピーができます。

オートフィルタの結果を見出しを除いてコピーする

実務で使う場合は、「見出しを除いた」フィルタ結果のコピーです。

「.Resize」と「.Offset」を使う

「.Resize」と「.Offset」を使えば、見出しを除いてコピーができます。

Sub TEST2()
    
    '東京をフィルタ
    Range("A1").AutoFilter 2, "東京"
    
    '見出し以外のフィルタ結果をセルにコピー
    With Range("A1").CurrentRegion
        .Resize(.Rows.Count - 1).Offset(1, 0).Copy Range("E1")
    End With
    
    'オートフィルタを解除
    Range("A1").AutoFilter
    
End Sub

表を用意しておきます。

表を用意

表を用意

では、VBAコードを実行してみます。

「見出しを除いて」フィルタ結果をコピーできた

「見出しを除いて」フィルタ結果をコピーできた

「見出しを除いて」フィルタ結果をコピーできました。

ちょっとややこしいので、手順を追って解説してみます。

手順を解説

手順の解説です。

「東京」をフィルタ

最初に「東京」をフィルタします。

'東京でフィルタ
Range("A1").AutoFilter 2, "東京"

実行します。

「東京」をフィルタ

「東京」でフィルタができます。

「.Resize」で1行小さくする

「.Resize」で1行小さくします。

'全てのセル範囲
With Range("A1").CurrentRegion
    '行数を「1つだけ減らす」
    .Resize(.Rows.Count - 1).Select
End With

「Range("A1").CurrentRegion.Rows.Count」で行数がでますので、これから「1」を引きます。

実行してみます。

「.Resize」で1行小さくする

ちょっとわかりづらいので、フィルタを解除します。

フィルタを解除して確認

1行だけ小さくなっていますね。

「.Offset」で1行下に移動する

次は、「.Offset」で1行下に移動します。

'全てのセル範囲
With Range("A1").CurrentRegion
    '行数を1つだけ減らして「1行下に移動」する
    .Resize(.Rows.Count - 1).Offset(1, 0).Select
End With

実行してみます。

「.Offset」で1行下に移動する

「.Offset」で1行下に移動できています。

フィルタ結果をコピー

フィルタ結果をコピーします。

'見出しを除くフィルタ結果をコピー
With Range("A1").CurrentRegion
    .Resize(.Rows.Count - 1).Offset(1, 0).Copy
End With

実行します。

フィルタ結果をコピー

フィルタ結果がコピーモードになりました。

セルに貼り付ける

セルに貼り付けます。

'見出しを除くフィルタ結果をコピーして貼り付け
With Range("A1").CurrentRegion
    .Resize(.Rows.Count - 1).Offset(1, 0).Copy Range("E1")
End With

実行します。

セルに貼り付ける

見出しを除くフィルタ結果を、セルに貼り付けることができました。

オートフィルタを解除

オートフィルタを解除します。

'オートフィルタを解除
Range("A1").AutoFilter

実行します。

オートフィルタを解除

オートフィルタを解除できました。

こんな感じで、見出しを除いてフィルタ結果をコピーすることができます。

フィルタ結果がある場合だけコピーする

少し注意するポイントで、フィルタ結果がある場合だけコピーする必要があります。

フィルタ結果がない場合すべてコピーされちゃう

フィルタ結果がない場合に、コピーをしてしまうと、すべてコピーされてしまいます。

Sub TEST3()
    
    '表にないデータでフィルタ
    Range("A1").AutoFilter 2, "沖縄"
    
    '見出しを除くフィルタ結果をセルにコピー
    With Range("A1").CurrentRegion
        .Resize(.Rows.Count - 1).Offset(1, 0).Copy Range("E1") '←データ全てを貼り付けてしまう
    End With
    
    'フィルタ解除
    Range("A1").AutoFilter
    
End Sub

表を用意します。

表を用意

実行してみます。

フィルタ結果がない場合すべてコピーされる

見出し以外のデータがコピーされてしまいました。

フィルタ結果がある場合だけコピーする

フィルタ結果を「WorksheetFunction.Subtotal(3, Range("A:A"))」で、カウントして、結果が「1より大きい」場合に、実行します。

Sub TEST4()
    
    '表にないデータでフィルタ
    Range("A1").AutoFilter 2, "沖縄"
    
    'フィルタ結果がある場合だけ
    If WorksheetFunction.Subtotal(3, Range("A:A")) > 1 Then
        
        '見出しを除くフィルタ結果をセルにコピー
        With Range("A1").CurrentRegion
            .Resize(.Rows.Count - 1).Offset(1, 0).Copy Range("E1")
        End With
        
    End If
    
    'オートフィルタ解除
    Range("A1").AutoFilter
    
End Sub

表を用意します。

表を用意

途中まで実行します。

フィルタ結果がない場合、SubTotalの結果は「1」となります。

フィルタ結果がない場合、SubTotalの結果は「1」となる

では、VBAコードを実行した結果をみてみます。

フィルタ結果がない場合は、コピーしない

フィルタ結果がないので、コピーされていません。

こんな感じで、フィルタした後にフィルタ結果を「SubTotal」を使ってカウントすると、うまくコピーすることができます。

「SubTotal関数」を使ってフィルタ結果を判定する方法について、詳細はこちらでまとめています。

オートフィルタの結果を削除する

オートフィルタの結果を削除する、というのをやってみます。

やり方はコピーの場合と同じで、オートフィルタでフィルタして結果を削除します。

結果だけを削除できる

削除の場合も、フィルタした結果だけを削除することができます。

フィルタ結果を「.Delete」を使って削除してみます。

Sub TEST5()
        
    '東京をフィルタ
    Range("A1").AutoFilter 2, "東京"
    
    '見出しを除いてフィルタ結果を削除
    With Range("A1").CurrentRegion
        .Resize(.Rows.Count - 1).Offset(1, 0).Delete
    End With
    
    'オートフィルタを解除
    Range("A1").AutoFilter
    
End Sub

表を用意します。

表を用意

表を用意

実行してみます。

削除するときに確認の表示が出ます。

確認の表示が出る

確認の表示が出る

OKをクリックします。

OKでフィルタ結果を削除できた

OKでフィルタ結果を削除できた

フィルタ結果を削除できました。

表示が出ないようにする

表示が出るたびにクリックしていると面倒なので、表示をオフにします。

「Application.DisplayAlerts = False」を使います。

Sub TEST6()
    
    '東京をフィルタ
    Range("A1").AutoFilter 2, "東京"
    
    Application.DisplayAlerts = False '表示をオフにする
    
    '見出しを除いてフィルタ結果を削除
    With Range("A1").CurrentRegion
        .Resize(.Rows.Count - 1).Offset(1, 0).Delete
    End With
    
    Application.DisplayAlerts = True '表示をオンにする
    
    'オートフィルタを解除
    Range("A1").AutoFilter
    
End Sub

表を用意しておきます。

表を用意

表を用意

実行してみます。

表示を出さないで削除できた

表示が出ないで削除できた

表示を出さないでフィルタ結果を削除できました。

フィルタ結果がある場合だけ削除する

削除する場合もフィルタ結果がある場合だけ、削除する必要があります。

フィルタ結果がない場合すべて削除されちゃう

フィルタ結果がない場合に、削除してしまうと、見出し以外のすべてのデータが削除されてしまいます。

Sub TEST7()
    
    '表にないデータでフィルタ
    Range("A1").AutoFilter 2, "沖縄"
    
    Application.DisplayAlerts = False '表示をオフにする
    
    '見出しを除くフィルタ結果を削除
    With Range("A1").CurrentRegion
        .Resize(.Rows.Count - 1).Offset(1, 0).Delete '見出し以外のデータがすべて削除されちゃう
    End With
    
    Application.DisplayAlerts = True '表示をオンにする
    
    'オートフィルタ解除
    Range("A1").AutoFilter
    
End Sub

表を用意します。

表を用意

実行してみます。

フィルタ結果がない場合すべて削除されちゃう

すべて削除されてしまいました。

フィルタ結果がある場合だけ削除する

削除の場合も、「WorksheetFunction.Subtotal(3, Range("A:A"))」を使って、フィルタ結果をカウントします。

Sub TEST8()
        
    '表にないデータでフィルタ
    Range("A1").AutoFilter 2, "沖縄"
    
    'フィルタ結果がある場合
    If WorksheetFunction.Subtotal(3, Range("A:A")) > 1 Then
    
        Application.DisplayAlerts = False '表示をオフにする
    
        '見出しを除くフィルタ結果を削除
        With Range("A1").CurrentRegion
            .Resize(.Rows.Count - 1).Offset(1, 0).Delete
        End With
        
        Application.DisplayAlerts = True '表示をオンにする
    
    End If
    
    'オートフィルタを解除
    Range("A1").AutoFilter
    
End Sub

表を用意します。

表を用意

途中まで実行してみます。

フィルタ結果がない場合は、SubTotalの結果は、「1」となる

フィルタ結果がない場合は、SubTotalの結果は、「1」となる

では、VBAコードを実行した結果です。

フィルタ結果がない場合は削除しない

フィルタ結果がないので、そのままの状態になっていて、削除はされていません。

こんな感じで、フィルタ結果をコピーや削除する場合は、フィルタ結果があるかをカウントするというのを条件に入れましょう。

「SubTotal関数」を使ってフィルタ結果を判定する方法について、詳細はこちらでまとめています。

おわりに

この記事では、オートフィルタの結果をコピーや削除する方法について、ご紹介しました。

オートフィルタの結果をコピーするには「.Copy」で、削除する場合は「.Delete」を使います。

オートフィルタでフィルタすると、そのまま「コピー」や「削除」をすることができます。

「SpecialCells」で可視セルを選択は必要ないです。

コピーや削除をする際の注意点は、フィルタ結果がない場合は、実行しないようにする、といいうことです。

すべてのデータがコピーや削除されてしまうので、注意です。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す