スポンサードリンク

ExcelVBA
ExcelVBA

前回に書いたオートフィルタを使ったやり方で

絞り込んだ内容でのループを紹介すると書いてから

もう4年経ってしました・・・

すみません、色々と忙しくこちらのブログを更新できていませんでした。

何してんだかって感じですよね

それでは、オートフィルタで絞り込んだ内容でループ処理する方法をご紹介します。

スポンサードリンク

オートフィルタで絞り込んだ内容でループ処理する手順

  • 一度フィルタの絞り込みを解除する
  • フィルタをかける
  • 表示されている内容でループ処理する

1.一度フィルタの絞り込みを解除する

フィルター前の状態
フィルター前の状態

最初にフィルタの絞り込みを解除してください。

よく忘れてフィルタの絞り込みがかかったままになったせいでエラーが起きてしまうことがよくあったんですよ。

それを解決するために、まずは処理を行う前にフィルタを下記のように絞り込みを解除してくださいね

Public Sub RemoveFilterTest()

    '入力前にオートフィルタを全て表示
    If ActiveSheet.FilterMode = True Then
        ActiveSheet.ShowAllData
    End If

End Sub

2.フィルタをかける

フィルターをかけた後
フィルターをかけた後

次にフィルタをかけます。

Public Sub FilterTest()
    
    '東京都と神奈川県でフィルタをかける
    ActiveSheet.Range("A1").AutoFilter Field:=2, Criteria1:=Array("東京都", "神奈川県"), Operator:=xlFilterValues

End Sub

ここでは『東京都』と『神奈川県』でフィルターをかけます。

都会だけ表示させてみましょう。

3.フィルタをかけた範囲でループ処理を行う

フィルター範囲でのループ処理

次にフィルターをかけた状態で表示されたもののみで

ループを行いデータ処理を行ってみます。

今回は『東京都』と『神奈川県』でフィルターをかけた範囲を対象にループ処理を行います。

ループしながら商品番号を取得して取得した商品番号をメッセージボックスに表示させます。

Public Sub LoopFilterTest()

    Dim r As Range
    Dim msg As String
    
    'A1セルから始まる表の範囲を対象とする
    With ActiveSheet.Range("A1").CurrentRegion.Offset(1, 0)
       
        '絞り込んだ行の範囲を変数「r」(Rangeオブジェクト)に代入する
        For Each r In .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows
           
            '変数「r」の行範囲の左端から1列目の値を変数に格納し、うしろにカンマをつける
            msg = msg & r.Cells(1, 1) & ","
        
        Next r
    
    End With
    
    'メッセージを表示
    MsgBox msg

End Sub

こちらを実行すると以下のメッセージボックスが表示されます。

フィルターのループ処理結果
フィルターのループ処理結果

フィルターで絞り込んだ状態の結果がメッセージボックスに表示されましたね。

解説

    Dim r As Range

フィルターをかけて絞り込んだ範囲をRangeオブジェクトに入れるため

変数宣言でRangeオブジェクトを宣言しておきます。

'A1セルから始まる表の範囲を対象とする
With ActiveSheet.Range("A1").CurrentRegion.Offset(1, 0)

・・・
      
End With

Withステートメントを使ってフィルタをかけた後の範囲で

左上のセル番地を使用します。

今回は【A1】を指定しますよ。

次に『CurrentRegion』を指定します。

【A1】を基準として見える範囲を指定します。

これは【Ctrl+Shift+*】と同じ動きをしますよ。

CurrentRegion
CurrentRegion

そして『Offset(1, 0)』は『CurrentRegion』で指定した範囲から

1行下がった位置での範囲を意味しています。

ここだと見出しを省いた範囲となります。

見出しがなければ『Offset(1, 0)』はいらないかもですね。

つまり、『CurrentRegion』で指定した範囲は今回だと【A1:D14】になるけど

CurrentRegion
CurrentRegion

『Offset(1, 0)』を使うと1行ずれるため【A1:D22】になります。

Offset
Offset

ここで、ようやくループ処理するための準備ができました。

ついて来ていますか?

大丈夫ですか?

それではようやく今回のメインである

フィルターかけたあとのループ処理です。

'絞り込んだ行の範囲を変数「r」(Rangeオブジェクト)に代入する
For Each r In .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows
           
 '変数「r」の行範囲の左端から1列目の値を変数に格納し、うしろにカンマをつける
 msg = msg & r.Cells(1, 1) & ","
        
Next r

変数「r」のRangeオブジェクトで『SpecialCells(xlCellTypeVisible)』を付けると

表示されているセルを意味します。

『.Resize(.Rows.Count – 1)』を付けることで「r」のRangeオブジェクトの指定範囲の行を1行削ることができます。

なので、指定範囲【A1:D22】の行数である8行から1行削った7行でループさせます。

あとは《For Each》文なので行が終わるまでループ処理します。

これでフィルターかけた状態でループ処理を行うことができるようになりました。

オートフィルタで絞り込んだ内容でループ処理する方法のまとめ

VBAで処理する状況でオートフィルターで絞り込んだ内容で

様々な処理を行いたい時があると思うので

この方法を使うと便利に処理ができるようになりますよ。

また、元々の表の位置が【A1】から始まらなくても

そこは指定のセル番地を変えてもらえば同じような処理ができます。

今回は《For Each》文にある『Offset』、『Resize』の意味合いが

なかなか理解できなかったので覚書のようにして書きました。

ご参考になれば幸いです。

スポンサードリンク