大体でIT

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

大体でIT

Excel VBAで、オートフィルタの結果を操作する方法について、ご紹介します。絞りこんだ結果を操作するには、「SpecialCells」を使います。絞りこんだ結果を操作する際に、フィルタ結果があるかを判定して操作すると、エラーを出さないで操作できます。フィルタ結果の判定は、「SubTotal関数」を使うと簡単です。

はじめに

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

オートフィルタの結果を操作するには、「SpecialCells」を使います。

オートフィルタの結果を操作する場合は、フィルタ結果があるかを判定して、操作するとエラーを出さないで、操作することができます。

オートフィルタの判定をする場合は、「SubTotal関数」を使うと便利です。

では、オートフィルタの結果を操作する方法について、解説していきます。

この記事で紹介すること

  • VBAでオートフィルタの結果を操作する方法

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

目次

オートフィルタで絞りこんだ結果を操作する

オートフィルタで絞りこんだ結果を操作するには、「SpecialCells」を使います。

「SpecialCells」で表示行のみ操作

「SpecialCells」の引数に「xlCellTypeVisible」を入力すると、表示行のみ操作することができます。

Sub TEST1()
    
    '表示している値を選択
    Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Select
    
End Sub

フィルタした表を用意しておきます。

フィルタした表

フィルタした表

では、実行してみます。

フィルタ結果を選択

フィルタ結果を選択

選択した範囲が、とびとびになっています。

フィルタ結果のみを選択できていることがわかります。

「見出しを除く」表示セルを操作する

見出しは基本的に操作しないので、「見出しを除く」表示セルを操作してみます。

「.Resize」と「.Offset」を使って、「見出しを除く」セル範囲を選択します。

Sub TEST2()
    
    '見出しを除いて表示している値を選択
    With Range("A1").CurrentRegion
        .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).Select
    End With
    
End Sub

フィルタした表を用意しておきます。

フィルタした表

フィルタした表

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

見出しを除いてフィルタ結果を選択

見出しを除いてフィルタ結果を選択

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

「For Each」で1行ずつ操作

フィルタした結果を、1行ずつ操作するには、「For Each」を使って操作します。

「.Rows」で「フィルタした結果の行」を指定するのがポイントです。

Sub TEST3()
    
    With Range("A1").CurrentRegion
        '見出しを除いて表示している行をループ
        For Each a In .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows
            a.Select '選択
        Next
    End With
    
End Sub

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

1回のループごとの結果をみてみます。

最初に、見出しを除くフィルタ結果の「1行目」が選択されます。

1行目が選択される

1行目が選択される

1行目が選択されました。

次に、2行目が選択されます。

2行目が選択される

2行目が選択される

2行目が選択されました。

次に、3行目が選択されます。

3行目が選択される

3行目が選択される

3行目が選択されました。

こんな感じで、「For Each」と「.Rows」を使って、見出しを除くフィルタ結果を1行ずつ操作することができます。

絞りこんだ結果に連番を入力

1行ずつ操作するVBAコードを使って、絞りこんだ結果に「連番」を入力してみます。

Sub TEST4()
    
    With Range("A1").CurrentRegion
        '見出しを除いて表示している行をループ
        For Each a In .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows
            i = i + 1
            '行の1行4列目に連番を入力
            a.Cells(1, 4) = i
        Next
    End With
    
End Sub

「For Each」で取得したオブジェクト「a」は、「Cells(1,1)~Cells(1,4)」というように、操作ができます。

セルの位置のイメージ

セルの位置のイメージ

というようなイメージです。

フィルタした表を用意しておきます。

フィルタした表

フィルタした表

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

フィルタした結果に連番を入力

フィルタした結果に連番を入力

フィルタした結果に「連番」を入力できました。

フィルタして連番を入力

フィルタするところから、連番を入力するところまでをやってみます。

Sub TEST5()
    
    '「東京」でフィルタ
    Range("A1").AutoFilter 2, "東京"
    
    With Range("A1").CurrentRegion
        '見出しを除いて表示している行をループ
        For Each a In .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows
            i = i + 1
            '行の1行4列目に連番を入力
            a.Cells(1, 4) = i
        Next
    End With
    
    'フィルタを解除
    Range("A1").AutoFilter
    
End Sub

表を用意しておきます。

表を用意

表を用意

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

東京に連番を入力

東京に連番を入力

東京に連番を入力することができました。

オートフィルタの結果がある場合だけ操作する

オートフィルタの結果がある場合だけ操作してみます。

オートフィルタの結果がない場合に、「SpecialCells」を使うとエラーとなってしまいます。

なので、フィルタ結果があるかの判定をして、操作する必要があります。

オートフィルタの結果があるかを判定

オートフィルタの結果があるかを判定は、「SubTotal関数」を使うと便利です。

「SubTotal」で表示している数をカウント

「SubTotal」で表示している数をカウントしてみます。

セル範囲を「Range("A:A")」で「A列」を指定します。

カウントしたいので、引数には、「3」を入力します。

Sub TEST6()
    
    '表示している行数をカウント
    Debug.Print WorksheetFunction.Subtotal(3, Range("A:A"))
    
End Sub

フィルタした表を用意しておきます。

フィルタした表を用意

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

「SubTotal」で表示している数をカウント

結果は、「4」となりました。

「SubTotal」で表示している数をカウントできました。

フィルタ結果があるかを判定

フィルタ結果があるかを判定するには、「SubTotal関数」の結果が「1より大きい」かの条件を使います。

Sub TEST7()
    
    '表示している行数が「1より大きい」場合
    If WorksheetFunction.Subtotal(3, Range("A:A")) > 1 Then
        MsgBox "データあり"
    Else
        MsgBox "データなし"
    End If
    
End Sub

フィルタした表を用意しておきます。

フィルタした表を用意

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

フィルタ結果があるかを判定

結果は、「データあり」となりました。

これで、フィルタ結果があるかを判定できます。

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

結果を判定して連番を入力

結果を判定して連番を入力してみます。

「東京」をフィルタして、連番を振ってみます。

Sub TEST8()
    
    '「東京」でフィルタ
    Range("A1").AutoFilter 2, "東京"
    
    'フィルタ結果がある場合
    If WorksheetFunction.Subtotal(3, Range("A:A")) > 1 Then
        With Range("A1").CurrentRegion
            '見出しを除く表示している行をループ
            For Each a In .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows
                i = i + 1
                '行の1行4列目に連番を入力
                a.Cells(1, 4) = i
            Next
        End With
    End If
    
    'フィルタを解除
    Range("A1").AutoFilter
    
End Sub

表を用意しておきます。

表を用意

表を用意

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

フィルタ結果を判定して連番を入力

フィルタ結果を判定して連番を入力

フィルタ結果を判定して、連番を入力できました。

データなしの場合は変化なし

データなしの場合は、エラーを出さないで、変化なしとなります。

表にない「沖縄」でフィルタしてみます。

Sub TEST9()
    
    '「沖縄」でフィルタ
    Range("A1").AutoFilter 2, "沖縄"
    
    'フィルタ結果がある場合
    If WorksheetFunction.Subtotal(3, Range("A:A")) > 1 Then
        With Range("A1").CurrentRegion
            '見出しを除く表示している行をループ
            For Each a In .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows
                i = i + 1
                '行の1行4列目に連番を入力
                a.Cells(1, 4) = i
            Next
        End With
    End If
    
    'フィルタを解除
    Range("A1").AutoFilter
    
End Sub

表を用意しておきます。

表を用意

表を用意

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

フィルタ結果がない場合は変化なし

フィルタ結果がない場合は変化なし

フィルタ結果がない場合は変化なしで、エラーは出ないです。

おわりに

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

オートフィルタの結果を操作するには、「SpecialCells」を使います。

オートフィルタの結果を操作する場合は、フィルタ結果があるかを判定して、操作するとエラーを出さないで、操作することができます。

オートフィルタの判定をする場合は、「SubTotal関数」を使うと便利です。

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

関連する記事から探す

カテゴリから探す

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

アーカイブから探す