vbaでシートを特定列の内容で複数ファイルに分割するコードを書くときにとても大切なことがある。

それは、EXCELは複数の行を削除するのが苦手であるということだ。特に、非連続、即ち連続していない複数行を大量に削除する場合は、とても時間がかかる。どうすればよいのか？簡単である。飛び飛びにならないように、事前にソートすれば良い。即ち、並び替えしておけば良いのだ。大量の行をEXCELで扱うときの大原則になる。 例えば、シートに営業部署があると仮定する。シートを営業部署ごとに別のEXCELファイルにしたいというのは、よくあるケースだ。 基本的なロジックは、シートをAutoFilterでフィルターし、フィルターした可視セルのみ、新ブックへコピーするのが良い。 可視セルを使わない場合、フィルターした以外の部署を削除するという荒技もある。その荒技を使う場合、大量の行を削除することになる。大量の分散した行を消すと時間がかかる。 そういうケースでは、事前に部署名でソートしておくのだ。分散が解消され、削除がスルっとあっという間に完了してしまう。

vbaでピボットをフィルターするならタイムラインがいい

vbaでピボットに左上隅のフィルターフィールドの項目で日付によるフィルターをかけたところ、笑えるぐらいの遅さのため、断念した。左上隅のフィルターフィールドの項目をフィルターするには、ひとつひとつの項目を表示（項目.visible=True）するのか、非表示（false）とするのかの設定しなければならない。そして、このFalse或いはTrueを設定するのに恐ろしく時間がかかるためだ。一つやるのに時間がかかる。数秒かかる。数個の商品ぐらいならば、高速処理できるが、日付を複数、非表示にするといったケースでは全く使えない。本当に使えない。

    Dim i As Long
    Dim pt As PivotTable
    Dim pf As PivotField
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("商品")　　
        itemsToHide = Array("りんご", "そば", "ハンバーガー","ラーメン")
        pf.ClearAllFilters
        For i = LBound(itemsToHide) To UBound(itemsToHide)
            pf.PivotItems(itemsToHide(i)).Visible = False    'ItemsToHideの項目のみ非表示にする。
        Next i
    End With
    Set pt = Nothing

数多の試行錯誤で、辿り着いたのは、左上隅のフィルターでななく、タイムラインとPivotFilters.Add2による日付フィルターであった。こちらは、とても高速で処理してくれる。

Sub TestAddPivotFilter2()
    AddPivotFilter2("2026/01/01","2026/01/31")
End Sub
Sub AddPivotFilter2(sDate As String,eDate As String)
    Dim pt As PivotTable
    Dim pf As PivotField
    Set pt = ActiveSheet.PivotTables(1)
    With pt.PivotFields("日付")
        .ClearAllFilters 
        .PivotFilters.Add2 _
            Type:=xlDateBetween, _
            Value1:= sDate, Value2:= eDate
    End With
End Sub

あとは、ピボットテーブルの分析タブで、タイムラインを作成すればOKだ。sDate,eDateを変えるだけで自在に高速フィルターをかけることができる。 実は、空白の日付があると、それをフィルターすることができない。じゃ、どうするというと、例えば、遠い未来の日付にすることで、フィルターすることが可能となる。 空白の日付は、よく発生する。リアルワールドでは実にしばしば、発生する。何かの完了日とすれば、その処理が完了していないことを意味する空白の日付が誕生するわけだ。マイクロソフトではこの空白の日付がそのままでは、フィルターすることができない。ので、未来の日付にしておくわけだ。

vbaでPIVOTテーブルを操作すると、ピボットテーブルレポートの更新が完了するまでお待ちくださいと怒られた。さて、どうする？

　ピボットテーブルの更新がバックグラウンドで、勝手に行われている可能性がある。そこで、ピボットテーブルのプロパティを調べて、バックグラウンドで更新するというオプションにレ点（チェック）があったら、それをを外せば、勝手に更新されなくなるので、「更新が完了するまで待て」とは言われなくなるはずである。どうだろうか？

accessのテーブルの外部リンクを更新するにはリンクマネージャのポップアウト画面で、左下の「リンク先を更新するためのプロンプトを毎回表示する(A)のチェックボックスにチェック印を入れよ。

accessのテーブルの外部リンクを更新するにはリンクマネージャのポップアウト画面で、左下の「リンク先を更新するためのプロンプトを毎回表示する(A)のチェックボックスにチェック印を入れよ。

excel Cm+Bnという式でBn=空白の場合、式がerrorになる。SUM(Cm,Bn)にすればerrorにならず、Cmの値が取得できる。

excel Cm+Bnという式でBn=空白の場合、式がerrorになる。SUM(Cm,Bn)にすればerrorにならず、Cmの値が取得できる。 これを使えば、グラフを作るケースで重宝する。

macOSにはsqliteがデフォルトで入ってる。

macOSにはsqliteがデフォルトで入ってる。

vba debug のノウハウのあれこれ

• vbaのデバッグ・ウィンドウでの便利なコマンド
  • ?ActiveWorkBook.Name
  • ?ActiveSheet.Name
  • ?Sheets("Sheet_Name").CurrentReagion.address
• .copyすると、ActiveSheetが切り替わる。
• wb.closeするとひとつ前のActiveSheetになる。ちゃんと、スタックされている。
• 今どこシートがアクティブなのかを意識しないと、プログラムが暴走しだす。
• ちょいちょいDebug.printせよ。
• 「インデックスが有効な範囲にありません。」が出たら、シート名やブック名などのスペルが間違えている。スペルを確認せよ。
• 列の削除は、一遍にやる。Columns("A:A,C:C,E:E,F:J").Delete　そして、その前にフィルターは外しておけ。フィルターがかかっていると、怪奇現象を誘発する。
• 不要な列は非表示にしてコピペせよ。
• menuでの言い方　Excelブック(.xlsx) Excel97-2003ブック(.xls)
• vlookupでの列コピーは、application.vlookupを使う。参照シートをコピーし、VLOOKUP関数を埋め込むのではなく・・・なんでやろ？
• 「このブックには更新できないリンクが１つ以上含まれています。」というメッセージを抑止するにはopenする前にUpdateLinks:=Falseを指定する。openをApplication.DisplayAlerts = Falseとpplication.DisplayAlerts = trueで挟んでおくという手もある。