【Excel】個人用マクロブックで効率化～タイトル行のフィルタと固定～

はじめに

Excel業務を日々行う中で、「いろんなブックにおいて共通で頻繁に行う操作」というのが存在します。
このような作業をマクロ化して「個人用マクロブック」に登録することで、すべてのブックにおいて同じマクロを実行できるようになり、業務効率化につながります。
私は表形式のデータを扱うことが多いので、本稿では「先頭行にフィルタをかけて表示を固定する」という作業を個人用マクロブックに登録し、いつでも使える状態にしてみます。

個人用マクロブックとは

個人用マクロブックは、すべてのExcelファイルに対して汎用的に使うことができるマクロブックです。後述しますが、ユーザーのローカルフォルダに保存されます。
使い方としてはアドインと近いのですが、アドインとして作るほどでもないもの、他人と共有しなくてもよいもの、自分の業務を手っ取り早く効率化したいときに使うもの、という感覚です。

作成～タイトル行のフィルタと固定

それでは早速作ってみましょう。
先述の通り、私は表形式のデータを扱うことが多いので、「１行目のタイトル行にフィルターをかけ、表示を固定する」という地味な作業をマクロに記録してみます。

テストデータを開き、「開発」タブ＞「マクロの記録」をクリック。

そうすると下記のウィンドウが出てくるので、ここで「個人用マクロブック」を選択します。

その後、登録したい操作を手動で実行し、「記録終了」をクリックします。

VBE（Visual Basic Editor）をひらくと、「PERSONAL.XLSB」というものが生成されています。こちらが個人用マクロブックです。

さて、「先頭行にフィルタをかけて固定」の作業を行った結果、記録されたソースコードはこのようになっていました。

Sub title_filter()
' title_filter Macro

    Selection.AutoFilter
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
End Sub

いったんこれで実行してみます。テストデータを開き（以下に出てくるデータはいずれもテストデータです）

マクロを実行してみると、

無事にタイトル行にフィルタがかかり、表示が固定されました！

…が、こちらいくつか考慮漏れをしておりまして、
まず空白行があるデータの場合はちゃんとフィルタがかかるのか？という問題があります。
試しに空白行を作ってテストしてみます。

実行すると、

…はい、変なところにフィルターが来ましたね。
実行時に、空白を挟んで下のブロックにあるセルを選択していたので、そのブロックの一番上の行にフィルタがかかりました。
先頭行は先頭行で固定されています。

（そもそも空白のあるデータはデータとしてNGではあるのですが、一旦そこは置いておいて）空白行があっても問題なく動くようにしてみたいと思います。
自動で記録されたマクロを、下記のように「範囲を指定してフィルタをかける」処理に変更します。

Sub title_filter()

    Dim row As Integer
    Dim col As Integer
    
    row = Cells(Rows.Count, 1).End(xlUp).row　　'最終行の取得
    col = Cells(1, Columns.Count).End(xlToLeft).Column　　'最終列の取得
    
    Range(Cells(1, 1), Cells(row, col)).AutoFilter　　'最終行・最終列まで範囲指定してフィルタ
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
End Sub

空白行ありのデータで実行してみます。

問題なく空白行も含めてフィルタがかかっているのが分かります。

自動記録されたマクロのままだと、他にも「データのどこかの行が選択されてしまっていた」という場合など、意図しない位置にフィルタがかかってしまう可能性があります。
範囲指定をすることで、確実に対象範囲にフィルタをかけることができます。

そしてもう一点、先頭行の表示を固定するこの部分です。

    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True

データの先頭行ではなく、中途半端な場所を表示しているタイミングでマクロを実行すると、「今表示されている一番上の行」が固定されてしまいます。
たとえば下記は、34行目の表示が固定されてしまっている状態です。

というわけで、表示を固定する前にセルA1を選択した状態にしておきます。

  　Cells(1, 1).Select  'セルA1を選択している状態にする
　  With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True

上記に変更した上で、同じく34行目を表示画面の先頭行にした状態で実行してみると、

無事に１行目で固定されました。

以上を踏まえて、最終的に今回は下記のソースコードでマクロブックを登録します。
（間違いを含む可能性がありますので、コピーして使われる際は自己責任にてお願いいたします）

Sub title_filter()

'①データ範囲にフィルタ

    Dim row As Integer
    Dim col As Integer
    
    row = Cells(Rows.Count, 1).End(xlUp).row
    col = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Range(Cells(1, 1), Cells(row, col)).AutoFilter

 '②データの先頭行を固定  
 
    Cells(1, 1).Select
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
End Sub

個人用マクロブックの保存と活用

無事にマクロが作れたので、保存します。
VBEのファイルタブから保存を選択。

「PERSONAL.XLSB」は、Windows10環境では下記に保存されます。
C:\Users\ユーザー名\AppData\Roaming\Microsoft\Excel\XLSTART

これだけではまだあまり便利ではないので、このマクロをクイックアクセスツールバーに登録します。
Excelのオプションよりクイックアクセスツールバーを選択し、「コマンドの選択」を「マクロ」、先ほど保存したブックを選択したら「追加」をクリックします。

これで、どのExcelファイルを開いても、今回登録したマクロがクイックアクセスツールバーに表示されるようになりました。いつでも実行可能です。

↑これが今回追加したマクロです。

別のマクロを追加してみる

PERSONAL.XLSBに別の処理を追加したい場合は、同じブックにそのまま書き連ねていきます。
（ここではマクロが追加されたときにどうなるか確認したいだけなので、メッセージボックスに「test」と表示させるだけの簡単なものを追加してみています。）

先ほどクイックアクセスツールバーに登録したボタンのクリックで、新規追加分まで勝手に実行されてしまったらどうしましょうと思っていたのですが、もちろんそんなことはありません。
このような感じで、それぞれのマクロを登録することができます。

というわけで、他にも効率化できそうなものがあれば、随時クイックアクセスツールバーに追加していくと便利かと思います。

おわりに

以前本ブログでもExcelアドインについては紹介していたのですが、個人用マクロブックは同じような機能でもう少しカジュアルに使えるのではないかなと思っています。
Excel業務で似たような手順の単純作業に時間がかかっているのであれば、ぜひ使ってみてください！
※テストデータはこちらにて作成させていただきました。

Season 1