前回の記事では、PivotCacheオブジェクトのRefreshメソッドとWorksheet.Changeイベントを組み合わせて、シートが更新されたらピボットテーブルが自動的に更新させる方法をご紹介しました。
しかし、前回記事の最後でも説明した通りに、前回ご紹介したコードだと、シート上でピボットテーブルのデータソースに指定された範囲以外が変更されたときにもピボットテーブルの更新処理が実行されてしまいます。
本来実行されなくてもよい処理なら、させないようにしておきたいですよね。
ということで今回は、Intersectメソッドで、変更を加えたセル範囲が、指定の範囲に含まれているかをキャッチする方法をご紹介します。
Intersectメソッドの記述方法
今回使用するのがApplicationオブジェクトのIntersectメソッド。
このメソッドは、2つ以上のセル範囲が交差する範囲をRange型で返してくれるメソッドです。
範囲同士が交差していない場合は、Nothingが返ってきます。
パラメータの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の場合。
範囲Aと範囲Bは、B2セルが交差していますね。
この時、Intersectメソッドを使ってみると…
Set rngPvt = Intersect(.Range("A3:C3"), .Range("B1:B8"))
このように、二つの範囲が交差している、B2セルを返してくれますね。
では次。
こちらの範囲Cと範囲Dは、C3セルからD4セルの範囲が交差していますね。
Intersectメソッドで見てみると…
Set rngPvt = Intersect(.Range("A3:D4"), .Range("C1:E8"))
3つの範囲が重なっている場合
では、こちらの場合はどうでしょうか。
E、F、Gの3つの範囲が交差していますね。
この範囲を、Intersectメソッドのパラメータにセットしてみると…
Set rngPvt = Intersect(.Range("B2:F10"), .Range("C3:E9"), .Range("D5:D7"))
交差しない範囲の場合
最後はこちら。
こちらの範囲Hと範囲Iは、交差していません。
Intersectメソッドを使ってみると…
Set rngPvt = Intersect(.Range("B3:B6"), .Range("D1:D4"))
Worksheet.Changeイベントと組み合わせてみる
前回のおさらい
まずは、前回のコードのおさらいです。
コードがこちら。
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メソッドを追加してみましょう！
ピボットテーブルが更新されたことが分かるように、「更新したよ」というメッセージが表示できるようにしておきましょう。
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メソッドは、今回のようにイベントの実行トリガー以外にも便利な用途が沢山ありますよ。
それでは、最後までお読みいただきありがとうございました！
