－ちょっと使えるネタを紹介－
Excel VBAで、オートフィルタの結果を操作する方法について、ご紹介します。絞りこんだ結果を操作するには、「SpecialCells」を使います。絞りこんだ結果を操作する際に、フィルタ結果があるかを判定して操作すると、エラーを出さないで操作できます。フィルタ結果の判定は、「SubTotal関数」を使うと簡単です。
この記事では、オートフィルタの結果を操作する方法について、ご紹介します。
オートフィルタの結果を操作するには、「SpecialCells」を使います。
オートフィルタの結果を操作する場合は、フィルタ結果があるかを判定して、操作するとエラーを出さないで、操作することができます。
オートフィルタの判定をする場合は、「SubTotal関数」を使うと便利です。
では、オートフィルタの結果を操作する方法について、解説していきます。
この記事で紹介すること
目次から見たい項目へ移動すると便利ですよ。
目次
オートフィルタで絞りこんだ結果を操作するには、「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コードを実行してみます。
「見出しを除いて」フィルタ結果を選択できました。
フィルタした結果を、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行目が選択されました。
次に、2行目が選択されます。
2行目が選択されました。
次に、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」で表示している数をカウントしてみます。
セル範囲を「Range("A:A")」で「A列」を指定します。
カウントしたいので、引数には、「3」を入力します。
Sub TEST6()
'表示している行数をカウント
Debug.Print WorksheetFunction.Subtotal(3, Range("A:A"))
End Sub
フィルタした表を用意しておきます。
では、VBAコードを実行してみます。
結果は、「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関数」を使うと便利です。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する