Excel VBA ピボットテーブルを操作する【列幅自動調整編】

今回もピボットテーブルで是非設定しておきたい項目について解説します。
ピボットテーブルのデフォルト状態では、更新時に列幅が自動調整されるようになっています。せっかく調整していた列幅が、自動調整時に崩れてしまいます。そうならないように、自動調整されない設定にしたいのでその方法を解説します。

くるみこ
くるみこ

この部分も毎回手動で設定している部分です。これもまた手間がかかっているところなので解消できるように勉強しましょう(^^)/

これ気になっている部分でした(^^; 今回もよろしくお願いします!

【この記事でわかること
・ピボットテーブル更新時に列幅を自動調整しないようにする方法がわかります

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

くるみこ
くるみこ

前回記事は、ピボットテーブルオプションの設定をVBAマクロで行う方法と「従来のピボットテーブルレイアウトを使用する」に設定する方法などについての解説記事でした。よければ覗いてみてね(^^)/

ボピボットテーブル更新時に列幅を自動調整させない方法

最初に一例として、ピボットテーブルを更新した時のGIF画像を見てください。

更新と同時に、列幅も自動調整されたために「縮小表示」に設定していたのにそれを無視して、幅が伸びてしまいました(-_-;)これが許せません!
これでまた表示を調整しなくてはいけなくなってしまいました!

こうならないように、いつも手動で[ピボットテーブルオプション]ダイアログの[更新時に列幅を自動調整する]チェックボックスを外して「自動調整」しないように設定を変更しています。

この[更新時に列幅を自動調整する]チェックボックスを、VBAで操作できれば列幅を自動調整しないようにすることができるので調整してみます。

「マクロの記録」を確認します

実は「マクロの記録」は前回記事「Excel VBA ピボットテーブルを操作する【オプション設定】」で実行して記録していますのでそのまま使っていきます。
この部分です。

・レイアウトと書式
  更新時に列幅を自動調整する
    ActiveSheet.PivotTables(“ピボットテーブル1”).HasAutoFormat = False

この、PivotTableオブジェクトのHasAutoFormatプロパティをFalseにすれば、「更新時に列幅を自動調整する」チェックボックスのチェックが外れ、自動調整しなくなります

更新時に列幅を自動調整しないようにするコード

HasAutoFormatプロパティを変更するマクロ

'更新時に列幅を自動調整しないようにする
Sub setHasAutoFormat()
    ActiveSheet.PivotTables(1).HasAutoFormat = False
End Sub

たったこれだけでOKです。

他の設定と同時に指定する場合(あくまで一例です)

他の設定と同時に指定する場合は次のようにします。

'他の設定と同時に「更新時に列幅を自動調整しない」ようにする
Sub setpivotFormatSample()
    Dim pvt As PivotTable
    Set pvt = ActiveSheet.PivotTables(1)
    With pvt
        .HasAutoFormat = False    '列幅を自動更新しない
        .InGridDropZones = True 'グリッド内でフィールドのドラッグを可能にするプロパティ
        .RowAxisLayout xlTabularRow  '表形式にするメソッド
        .ColumnGrand = False      '列の総計を削除
        .RowGrand = False         '行の総計を削除
    End With
End Sub

全てのピボットテーブルに適用する方法

全てのピボットテーブルに対して「更新時に列幅を自動調整しない」ようにするならこんな感じです。

'すべてに対して「更新時に列幅を自動調整しない」ようにする
Sub setHasAutoFormatAll()
    Dim ws As Worksheet
    Dim pvt As PivotTable
    For Each ws In Worksheets
     For Each pvt In ws.PivotTables
      pvt.HasAutoFormat = False
  Next pvt
 Next ws
End Sub

・PivotTable.HasAutoFormatプロパティをFalseにする処理をループで繰り返す方法です。
・5行目~、外側の For Each ws In Worksheets ~ Next は、アクティブなブックの全ワークシートWorksheetsコレクションに対するループ処理です。
・6行目~、内側の For Each pvt In ws.PivotTables ~ Next は、各シート上の全ピボットテーブルPivotTablesコレクションに対するループ処理です。

必要に応じて内側だけ(シート対象のループに設定するなどして流用できます

スポンサーリンク

まとめ(おわりに)

・いかがでしたでしょうか?
「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

どうですか。これで「ピボットテーブル」での苛立ちがだいぶ解消できたんじゃないでしょうか。他にも気になる部分があったら今までのコードに追加設定して行けばよいでしょう(^^)

ここまでに勉強したことが全部参考になっているのですんなり理解できました!もっといろいろ試して見たいと思います(^^)

くるみこ
くるみこ

次回は、ピボットテーブルの書式設定部分をもう少しだけ勉強したいと思います。楽しみにしていてね(^^)/

【今回分かったことは】
・ピボットテーブル更新時に列幅を自動調整しないようにする方法がわかりました

★★★ ランキング参加中! クリックしてね(^^)/ ★★★

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようにしています
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら(このリンク先)からご利用ください