【エクセルVBA】Intersectメソッドで範囲内のセルが変更されたことをキャッチする方法

eyecatch,vba,Intersect

皆様こんにちは、ノグチです。

前回の記事では、PivotCacheオブジェクトのRefreshメソッドとWorksheet.Changeイベントを組み合わせて、シートが更新されたらピボットテーブルが自動的に更新させる方法をご紹介しました。

【エクセルVBA】RefreshメソッドとRefreshTableメソッドでピボットテーブルを自動更新させてみよう
Worksheet.Changeイベントとピボットテーブル更新のPivotTableオブジェクトのRefreshTableを組み合わせて、シートが更新されたらピボットテーブルを自動更新させる方法をご紹介しています。同じピボットテーブル更新のPivotCacheオブジェクトのRefreshメソッドもご紹介していますよ。

しかし、前回記事の最後でも説明した通りに、前回ご紹介したコードだと、シート上でピボットテーブルのデータソースに指定された範囲以外が変更されたときにもピボットテーブルの更新処理が実行されてしまいます。

本来実行されなくてもよい処理なら、させないようにしておきたいですよね。

ということで今回は、Intersectメソッドで、変更を加えたセル範囲が、指定の範囲に含まれているかをキャッチする方法をご紹介します。

スポンサーリンク

Intersectメソッドの記述方法

今回使用するのがApplicationオブジェクトのIntersectメソッド

このメソッドは、2つ以上のセル範囲が交差する範囲をRange型で返してくれるメソッドです。

範囲同士が交差していない場合は、Nothingが返ってきます。

記述方法はこちら。
Applicationオブジェクト.Intersect(Range1, Range2…)

パラメータのRangeは、最大30まで指定できるようです。(Range1とRange2は必須)

指定した範囲が交差している範囲を返してくれるということは、返り値がある=交差している範囲がある=指定した範囲1が、範囲2に含まれている、と考えることができますね。

交差する範囲の例

では、こちらのコードを使って、実際に交差する範囲がIntersectメソッドでどう取得できるのか、試してみましょう。

Sub Test_Intersect()
Dim rngPvt As Range

With ActiveSheet
    Set rngPvt = Intersect(.Range(範囲1),  .Range(範囲2))
    If Not rngPvt Is Nothing Then
        MsgBox rngPvt.Address
    ElseIf rngPvt Is Nothing Then
        MsgBox "Nothing"
    End If
End With

End Sub

上のコードの5行目にある、Set~以降の範囲を変えて、試してみます。

2つの範囲が重なっている場合

まずはこちらの図のような範囲Aと範囲Bの場合。

Intersect,重なる

範囲Aと範囲Bは、B2セルが交差していますね

この時、Intersectメソッドを使ってみると…

Set rngPvt = Intersect(.Range("A3:C3"), .Range("B1:B8"))

Intersect,重なる、結果

このように、二つの範囲が交差している、B2セルを返してくれますね。

では次。

こちらの範囲Cと範囲Dは、C3セルからD4セルの範囲が交差していますね。

Intersect、重なる

Intersectメソッドで見てみると…

Set rngPvt = Intersect(.Range("A3:D4"), .Range("C1:E8"))

Intersect、重なる、結果

この通り、交差している範囲を返してくれます。

3つの範囲が重なっている場合

では、こちらの場合はどうでしょうか。

Intersect、重なる、3つ

E、F、Gの3つの範囲が交差していますね。

この範囲を、Intersectメソッドのパラメータにセットしてみると…

Set rngPvt = Intersect(.Range("B2:F10"), .Range("C3:E9"), .Range("D5:D7"))

Intersect、重なる、3つ、結果

ちゃんと、3つの範囲が交差した範囲を返してくれますね。

交差しない範囲の場合

最後はこちら。

こちらの範囲Hと範囲Iは、交差していません

Intersect、重ならない

Intersectメソッドを使ってみると…

Set rngPvt = Intersect(.Range("B3:B6"), .Range("D1:D4"))

Intersect,Nothing,重ならない

この通り、交差する範囲が無いので、Nothingが返ってきました。
これらの例でわかる通り、Intersectメソッドの返り値がNothingでなければ、パラメータに指定した2つ以上の範囲は重なっているということです。

Worksheet.Changeイベントと組み合わせてみる

では、Intersectメソッドのご紹介が済んだところで、今回の本題へ参りましょう。

前回のおさらい

まずは、前回のコードのおさらいです。

コードがこちら。

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
With ActiveSheet
    .PivotTables("売上").PivotCache.Refresh
End With
Application.EnableEvents = True

End Sub

そしてピボットテーブルとデータソースがこちらです。

ピボットテーブル,データソース

ピボットテーブルはA1セルからD21セルまでをデータソースとし、「売上」という名前をつけてあります。

前回記事でご紹介したコードでは、ピボットテーブルのデータソース範囲外のセルを更新した場合でもWorksheet.Changeイベントが実行されて、イベントに仕込んだピボットテーブルの更新処理が実行されてしまうのでしたね。

ピボットテーブルに関係するセルを更新した時だけ、ピボットテーブルを更新させたい…というのが前回の課題でした。

Intersectメソッドを追加

では、前回のコードに、今回ご紹介したIntersectメソッドを追加してみましょう!

ピボットテーブルが更新されたことが分かるように、「更新したよ」というメッセージが表示できるようにしておきましょう。

前回のWorksheet.Changeイベントに差し込んだコードに、Intersectメソッドを追加したコードがこちら。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngPvt As Range

With ActiveSheet
    Set rngPvt = Intersect(Target, .Range("$A$1:$D$21"))
    If Not rngPvt Is Nothing Then
        Application.EnableEvents = False
        .PivotTables("売上").PivotCache.Refresh
        MsgBox "更新しました!"
        Application.EnableEvents = True
    End If

End With
End Sub

これで、シート上のセルを変更してみましょう!

ピボットテーブルのデータソース範囲変更時のみ処理実行

まずは、ピボットテーブルのデータソース範囲外のセルの値を変更した場合から。

ピボットテーブルのデータソース範囲はA1セルからD21セルまでなので、その範囲外のF15セルを変更してみましょう。

F15セルに値を入力してエンターキーを押すと…

データソース変更

この通り、何も起きませんし、更新したよ!のメッセージも表示されません。

続いて、データソース範囲内のセルの値を変更してみましょう。

C5セルの値を変更してみると…

ピボット更新

この通り、ピボットテーブルが更新されて、メッセージも表示されました。

ピボットテーブルのデータソース範囲内が変更された場合のみ、ピボットテーブルを更新させる、という処理にできていますね!

最後に

今回は、Intersectメソッドを使って、ピボットテーブルのデータソース範囲のセルが変更されたときのみピボットテーブルを更新する、という方法をご紹介しました。

このIntersectメソッドは、今回のようにイベントの実行トリガー以外にも便利な用途が沢山ありますよ。

それでは、最後までお読みいただきありがとうございました!

タイトルとURLをコピーしました