ワークシートを再計算する

特定のワークシートの関数を再計算させるコード

'アクティブシートを再計算
ActiveSheet.Calculate
'特定のシートを再計算
Worksheets("Sheet1").Calculate
'指定のブックの特定のシートを再計算
Workbooks("Book1.xlsx").Worksheets("Sheet1").Calculate

ワークシートの再計算を行うには「Calculate」メソッドを使用します
このコードを実行すると、アクティブシートに含まれる関数が全て再計算されます
処理中等に手動に切り替えた関数計算をコード中に更新する場合によく利用します

ワークシートオブジェクトを任意に指定することで、特定のシートだけを再計算を行うことが可能です
2つ目の例コードはワークシート名を指定することで特定のシートを対象としています

また3つ目のコードの様に、ブックから指定することで別のブックのシートを再計算させることも可能です
これを応用することで、特定のブックの全シートの再計算を行うことが出来ます
以下の記事にて解説しています

すべてのブックやセル単体で再計算を行うことも可能です
以下の記事で解説しています

特定のブックの全シートを再計算する

複数のブックを開いた状態で特定のブックのみを再計算するコード

'ループ用の変数宣言
Dim zz対象Ws As Worksheet

'特定のブックの全シートを再計算
For Each zz対象Ws In Workbooks("Book1.xlsx").Worksheets
zz対象Ws.Calculate
Next zz対象Ws

再計算を行うには対象のオブジェクトに対して「Calculate」メソッドを使用します
しかし、Workbookオブジェクトにはこのメソッドがありません
そのため、複数のブックを開いた状態で1つだけのブックを再計算するには少し工夫が必要となります
それがこの記事コードになります

このコードを利用することで、複数のブックを開いた状態であっても特定のブックのみを再計算することが可能となるので極端に時間のかかる再計算の不要なブックを除外することが可能になります

コード解説

コードの流れ自体は非常に簡単なものです
Worksheetオブジェクトをループで再計算を実行していくだけです

'ループ用の変数宣言
Dim zz対象Ws As Worksheet

まずはループで利用するためのWorksheetオブジェクトを代入する変数を宣言します
この時の型はObject型でも構いませんが、Worksheet型にしておくとコード入力の際にインテリセンスが利用できるので便利です

'特定のブックの全シートを再計算
For Each zz対象Ws In Workbooks("Book1.xlsx").Worksheets
~~~
Next zz対象Ws

次にループ処理の個所です
ForEachループによって特定のWorkbookに含まれるWorksheetを全てループします

ここで大切なこととして、Excelのシートにはオブジェクトが複数あります
主にはWorksheetとSheetです
特に後者のSheetオブジェクトはマクロの記録でよく出てきます

For Each zz対象Ws In Workbooks("Book1.xlsx").Sheets
~~~
Next zz対象Ws

このコードの様にWorksheetオブジェクトでは無く、 Sheetオブジェクトとしてもほとんどの場合問題なく動作します

このSheetオブジェクトにはグラフシートやマクロシートなどの通常Excelで使用する方眼紙シート以外のものも含まれます
そのため再計算を行う目的であれば、Worksheetオブジェクトを指定する方が良いです

そして、「Workbooks(“Book1.xlsx”)」の引数の文字列を対象とするブックの名前に変更すれば任意のブックを指定することが出来ます

zz対象Ws.Calculate

ループ内処理はWorksheetオブジェクトに対してCalculateメソッドを実行しているだけです

この様にして複数のブックを開いた状態で特定のブックのみの全シートを再計算させることが出来ます
使いどころが限定的ですが、知っていれば処理時間を短くすることも可能なので有用だと思います

全ブックまとめて再計算するほうがええねん、という方は以下の記事を確認してください

指定セル以下の行を全て削除する

指定セル以下の行全てを削除・消去するコード、見出しを除外した範囲全てを初期化する際に使用します

'2行目以降を削除
Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete
'2行目以降を消去
Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Clear

関数などで参照しているセル範囲である場合にはこちらを利用してください

データ集計を行ったり、フィルタデータの貼り付けなどで元々あるデータは削除してデータを貼り付けする処理はよくあります
この際に見出し行は削除したくない場合があります
そんな場合に使用するコードです

指定セル以下の行全体を削除する動き
コード実行時の動き

このコードを使用すると2行目以降のすべてのセルを削除しますが、1行目はそのままなので見出しを再度作成したりする必要が無くなります

ちなみに画像ではテーブル機能のデータ範囲以下全てを削除しています
テーブル機能の場合はデータが無くても、データ範囲1行目には背景色の設定が表示されますので、実際にはちゃんと削除されています

データ範囲のみを選択する場合は以下の記事で解説しています

ですがこの選択範囲はデータ行のみになるので、もし未保存の削除セルがあった場合LastCellなどでうまく最終セルが取得できない場合があったり、コピー元のセル範囲がそういったセルを作成してしまっていたりすることがあるので、なるべく例コードの様にデータ範囲を初期化する場合は全てのセル範囲を指定する方が無難です

コード解説

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete

このコードは1行ステートメントですが、複数のプロパティを使用しているのでプロパティごとに分割して解説を行います

Range("A2").Select
コードを実行した場合

そのRangeオブジェクトの1つ目の引数はこれまたRangeオブジェクトとなっています
その引数にはA2が指定されています
なのでこの引数でSelectメソッドを実行すると画像のセルが選択されます
ここが始点のセルになるということですね
なのでここのA2を変更することで任意の行数に変更することが出来るということになります
A3にすれば3行目以降全てが削除対象となり、1・2行目はそのまま保持される事になります

Range("A" & Cells.Rows.Count).Select
コード実行後のセル選択

次の2つ目の引数にもRangeオブジェクトが指定されていますが、今度は単純にセルAddressが入力されているわけではありません

「Cells.Rows.Count」というのは、このシートに存在するセル範囲の行数を数えています
その数字の前にAが入力されているので、A列の最終行が指定されます
画像の通りで行数値は「1048576」となりA1048576セルが選択状態になっています
ここが終点セルとなります、要は以降全部ってことですね

Range(Range("A2"), Range("A" & Cells.Rows.Count)).Select
範囲選択された状態

この2つを始点と終点としてセル範囲指定をすることで、A2からA1048576までが範囲選択された状態となります
画像の様に2行目以降がすべて選択されていることが確認できます

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Select
対象範囲が選択された状態

ここまでの対象範囲の取得ではA列だけになってしまいます
そこでこの対象範囲を最終列まで広げていきます

EntireRowプロパティは、A列だけの範囲を列方向に広げてくれます
これを使用することで画像の通りに最終列まで範囲を広げることが出来ます

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete

最後にDeleteメソッドを使用して、その範囲を削除します
上記までで指定行範囲を取得できているので、そこに対して削除を実行します

この範囲を別のシートで関数等で参照している場合、セル参照がエラー値(#REF!)になってしまいます
その場合はDeleteメソッドではなくClearメソッドを使用することでセルの初期化を行うことが出来ます

それが例コードの2つ目のものです
単純にメソッドをClearメソッドに変更しただけですが、これを行うと書式含めすべてが初期化されるので、見た目的には削除とほぼ同等の効果です

上記に記載していますが、このメソッドではセルが削除されないため、関数などの参照を行っている場合に自動的に範囲が修正されたり「#REF!」エラーになることを防ぐことが出来ます

作り込みの凄まじい関数ファイルなどで処理を行う場合はこちらを利用しておいた方が無難です
なんせ作り込まれた関数ファイルはVBAより遥かに難解ですので

なおブックのサイズにもよりますが、こういった削除処理を行った際は処理最後にでもブックの上書き保存を行っておいた方が良いです

なぜなら、削除したセル範囲のLastCellなどはそこでしか更新されないためです
またこれを行うことで、スクロールバーの可動域も新しいデータ範囲に調整されますので、あまりにも削除前とのデータ範囲に差がある場合にはユーザーにとっても意味のある保存と言えます

ただ保存はサーバー上であったり、ブックサイズが大きいと時間がかかってしまい
待ち時間がユーザーにとってのストレスになることもあることは認識しておきましょう

個人用マクロブックを読み込まない

個人用マクロブックが読み込みされなくなった時の対処について

ファイルタブを選択

まずは落ち着いてExcelを起動してください
そしてファイルタブを選択します

オプションを選択

次に左側メニューの一番下のオプションをワンクリックします
焦ってダブルクリックしてしまっても問題無いので落ち着いてクリックしましょう

アドインを選択

開いたウィンドウのExcelのオプションのメニューからアドインを選択します
手の震えを無理に抑えなくても…(しつこい)

オプションを選択

表示された画面の下部にある、管理項目を確認します
ここの選択肢から画像の「使用できないアイテム」を選択して、設定ボタンをクリックします

設定の確認画面

ここに「PERSONAL.XLSB」があれば、選択して下にある有効にするボタンをクリックしてください

これで再起動すると読み込みが正常に行われるようになります
ここにない場合は、マクロブックが存在していないかもしれません
こうなったらお手上げですね…この操作で復活出来ることを祈っております

先日発生した話

個人用マクロブックとは、そのPCにおいてExcelを起動すると自動的に読み込みされる
マクロ保存用のブックの事です。
個人用と名前の通りで、個人で利用するには非常に便利な機能です。

個人用のファイルは特定の個所に保存され、初期設定の状態でマクロセキュリティでは安全な場所として登録されているため起動時のマクロセキュリティ確認も必要ありません。
特に作成用のコードを保存しておくなど、作成に役立てる目的でも自分はよく使います。

そんな便利なファイルですが、いきなり使用が不能となりました。

これはかなり愕然としました
上記にあるように自分の仕事PCの個人用マクロブックはまさにこのサイトにあるような自分の知識の蓄え場でもあるため、結構な量のコードやフォームが存在しています。
日々仕事の中で作成した処理などで閃いたことや後に残しておきたいものがそこには貯め込まれています。

これを一気に失ったのです。

何か月もかけて作ったExcelVBA処理の比では無く
マジでくじけそうになるほどの衝撃でした。
「からっぽやないかい…」
「ゴミ箱も何も入ってへんやないかい…」

正直なところ、作成したExcelVBA処理ならあきらめもつきます
所詮はこのマクロブックからコピペなどして作成したモノですから

それほどの重要なファイルが消失したと思っていました

と、いうのも
このExcelの起動時読み込みには実際にはユーザー・Excel・Officeという3つの種類があります
これのうち、利用されるのが最初のユーザーになります

それも認識が無かったため、Excelの起動フォルダを確認しに行って
ファイルが存在していないと勘違いをしていました

実際にはユーザーの読み込みフォルダ内にファイルが存在することは確認できました

ところが、ところがです
この記事のタイトルの現象に気づいたのです
そこにファイルが間違いなく存在しているのに、なぜかExcel起動で読み込みが行われない

個人用マクロブックをダブルクリックで開いてあげると、VBAが実行できます

なので、もう一生こうして自動的に読み込まれていたファイルを手動で毎回起動しないといけないんだな、と半ば諦めかけていました
なぜならファイルが無くなっていなかったのでVBAコードが残っていた事に安堵していたからです
もうとにかく生きていてさえくれれば、それでええ…
そんな気持ちでした

ですが、ふと思い出しました
Google先生に聞いたら分かるんちゃうか、と

実際すぐに解決しました
とはいっても、画像のあるサイトが無かったので焦っていた時にはそのページを見ても
ちゃう、これが原因ちゃう
と、すぐに別のことを確認してしまっていました

結果無駄な時間を過ごすことになってしまいました

とにかく、ファイルが消失してなくて良かった~
VBA処理を作ったファイルはバックアップ取っていたけど、個人用マクロブックのバックアップは取っていませんでした
これを機に個人用マクロブックもバックアップを取るようにします
みなさんも個人用マクロブックを大切にして下さい

値の入力されたセルだけをクリアする

SpecialCellsメソッドの引数xlCellTypeConstantsを利用して値入力されたセルだけをクリアする方法について

'指定範囲内のみを対象とする
Range("A1:C3").SpecialCells(xlCellTypeConstants).ClearContents
'すべての使用済みセル範囲を対象とする
Cells.SpecialCells(xlCellTypeConstants).ClearContents

セル範囲の中で値の入力されたセルのみをクリアするには「SpecialCells」メソッドの引数「xlCellTypeConstants」を使用します

すでに関数によってある程度フォーマットが完成したシートに対して、入力値の部分だけを初期化する場合に利用します
単純にセル範囲全てをクリアしてしまうと、関数も消去されてしまうためです

なお、セル自体が数式が入力されているかを判定する方法は以下の記事で解説しています
ただ今回のような範囲に一括で処理を実行したい場合にはあまり向いていません

しかし、このメソッドもUsedRangeに影響を受けるため、既存のデータから上書き保存が許されないような処理の場合は記事コードはうまく動作しない場合がある点に注意が必要です
そのあたりに関しては以下の記事で解説していますので確認しておいてください

コード解説

値セルのみのデータの消去
値入力されたセルのみをクリアする動き

画像の動きを確認してください
前提としてC列はA列+B列の数式が入力されています
なので値入力された範囲はA2~B7となります

主にすでにあるフォーマットから入力範囲のデータを消去する場合に利用する場面が多いため記事コードでも前者の方が利用頻度は高いと思われます

'指定範囲内のみを対象とする
Range("A1:C3").SpecialCells(xlCellTypeConstants).ClearContents

このコードではA1~C3という範囲指定を行った上で、値のセルに対してClearContentsメソッドを実行して入力値を消去しています
画像ではA2~B3がその範囲に該当するため、そこが消去されています

こうして指定することでせっかく作った関数を消去せずに入力フォーマットを初期化できます

'すべての使用済みセル範囲を対象とする
Cells.SpecialCells(xlCellTypeConstants).ClearContents

こちらではシート全体のうち、使用済みセル範囲であるUsedRangeプロパティで取得される範囲の全ての値セルがクリアされます
この方法だと見出しや題目等の部分まで消去されるので、あまり利用頻度は無いかもしれません

ちなみにセル内に「=”見出し名”」というような入力を行えば、そのセルは数式と認定されるようになるので全ての範囲から値セルを消去しても問題なくなります
ただ全てを振り替えたり、修正があった場合に非常に面倒なので現実的な利用方法ではありません

この方法でセル範囲を限定すれば、ClearContentsメソッドを他のものに変更するだけで、値セルにだけ背景色を設定したりすることも簡単に行うことが出来ますので、アイデア次第で広がる汎用性のあるものだと思います
ただただUsedRangeに引っ張られる点を除けば、ですが・・・

数式の入力されたセルだけを取得する

SpecialCellsメソッドの引数xlCellTypeFormulasを利用して数式セルが入力されたセルだけを取得する

Debug.Print Cells.SpecialCells(xlCellTypeFormulas).Address

数式が入力されたセルを取得するには「SpecialCells」メソッドに引数「xlCellTypeFormulas」を使用します
この引数設定では数式が入力されたセル範囲を取得できます

このメソッドには利用時に注意点があります
それはUsedRangeプロパティが基準とされる点です
コードの解説とともに解説します

コード解説

数式が入力された範囲の取得
数式入力範囲の取得

記事コード含め合計4行を実行しています
前提として、シートの選択範囲が数式が入ったセルです
それ以外は空白セルは空白、数値が入っている部分とC列が数式です

記事コードが実行されると選択範囲と同じ範囲が取得されています
C2~C7のセル範囲が取得されます

ここで1つ注意したいのが、Cellsオブジェクトはワークシート全体のセル全てを指定しますが、内部的には自動的にUsedRangeプロパティに変換されます
ただこの引数設定ではあまり影響は無いかもしれません

ActiveCell.SpecialCells(xlCellTypeFormulas).Address
Range("A1").SpecialCells(xlCellTypeFormulas).Address
→→→ $C$2:$C$7

これら2行の指定でも同じくC列の入力範囲が取得されています
指定Rangeオブジェクトが単一の場合にはCellsオブジェクトと同様にUsedRangeプロパティに変換されて取得されます

ほとんどそういった利用は無いとは思いますが、数式かどうかの判定には利用できないですね

Range("A1:C3").SpecialCells(xlCellTypeFormulas).Address
→→→ $C$2:$C$3

ここではRangeオブジェクトで範囲指定を行っています
この場合はその範囲内で条件を満たすものが取得されます

A1~C3までのセル範囲のうち数式が入力されたセルはC2とC3となるため、その範囲が取得されています

この様に全ての範囲から取得を行うのではなく、限定された範囲から取得する場合はその範囲を指定してあげれば良いだけです

ただ今回の引数の際にはあまり影響は無いのですが、やはりUsedRangeプロパティの影響を受けています
指定範囲内であってもUsedRangeプロパティ外は検索対象に入りません

つまりIntersectメソッドを使ったときの様に、指定範囲とUsedRangeプロパティの範囲の重複する部分のみが対象となってしまいます

このメソッドを利用する場合にはとにかくUsedRangeプロパティへの注意が必要なため、以下の記事を参考にしてこのプロパティの動きをしっかり理解しておいてください

セル内容が数式かどうかを判定する

セルの入力内容が数式かどうかを判定するためのプロパティについて

'セルが数式かどうかを判定する(Trueなら数式)
Debug.Print Range("A1").HasFormula

セル内容が数式かどうかを判定するには「HasFormula」プロパティを使用します
Trueならセル内容は数式が入力されています、値等の数式以外であればFalseが返されます

セルの入力値には大きく分けて数式と値が存在しますが、それを判定するためのプロパティです
これはIs~~関数等のものではなく、Rangeオブジェクトのプロパティである点に認識が混同しないように注意が必要です

ちなみに数式と関数はこのプロパティ上は同一で判断します

コード解説

HasFormulaプロパティの取得の動き
プロパティ取得の動き

画像の動きを確認してください

まず前提としてB1セルにはA1セルに表示された数式が入力されています
ただ空白を返しているだけの数式になります
B2セルには何も入力されていません

なので空白かどうかの判定に関しては出力されるのは「True」が出力されています

これは空白チェック時の要注意点の1つなのですが、数式の結果が空白を返している場合は、セルが空白と混同して判定されてしまいます
この場合は空白かどうかを判定するだけでは不十分です

Debug.Print Range("B1").HasFormula
→→→ True

次にプロパティを利用した判定を取得してみます
この場合はB1には数式が入力されているため、Trueが返されます

ここでさらに空白チェックを複合的に条件を立てることにより、数式で空白が返されたものは除外することが出来ます

Debug.Print Range("B2").HasFormula
→→→ False

B2に関しては何も入力されていないため数式では無いのでFalseが返されます
ここは数式でなければ入力値があったとしても値であればFalseとなります

このプロパティを利用することで、指定セルから数式の空白を含めた空白を探すような特殊な場面で活用できます

ループ処理で不要行の削除を行う

ループ処理で条件判定を行って、不要な行を削除するちょっとした処理とその際の注意点の説明

Dim zzループ用セル As Range 'Forループ用のRange型変数
Dim zz対象セル As Range  '削除対象のセルの代入先のRange型変数

'指定セル範囲を全てループする
For Each zzループ用セル In Range("A2:A16")
'セルが空白なら削除対象とする
If zzループ用セル = "" Then
'対象セル変数が取得されているかで分岐
If zz対象セル Is Nothing Then
Set zz対象セル = zzループ用セル
Else
'取得されていればUnionメソッドを使用してセル範囲を追加
Set zz対象セル = Union(zz対象セル, zzループ用セル)
End If
Else: End If
Next zzループ用セル

'ループ終了後にセルが取得されているかを判定
If Not zz対象セル Is Nothing Then
'取得されていれば対象範囲を削除する
zz対象セル.EntireRow.Delete
'↓テーブル機能利用時はテーブルの列範囲内でなければエラーとなるので以下を利用する
'Intersect(zz対象セル.EntireRow, Range("A:D")).Delete
Set zz対象セル = Nothing
Else: End If

ループ処理で判定を行いつつ不要行の削除や挿入を行う際には、実際の削除処理を実行するタイミングが処理上で注意が必要です

というのも、セルのループをする場合は基本はForEachループを利用しますが、このループ処理では最初に指定のセル範囲を代入することになります
ループ中に削除を行うと、代入したセル範囲が矛盾を起こしてしまいます
処理の組み方次第では実行時エラーとなります

ループ処理で削除や挿入を行う際には、ループ処理中にメソッドは実行せずに対象セルを取得して、ループが終了してから一括で実行することで矛盾の発生を無くす事と処理速度の向上を実現できます

その際に利用するのはRange型の変数だけです
この変数に削除対象となるセル自体を代入していきます
ただ削除対象をそのままループで代入しても、前回のセルが上書きされてしまいますのでUnionメソッドを利用してセル範囲を追加していきます

ループ判定が終了した段階で、対象セルが取得されていればその範囲に対して一括でDeleteメソッドで削除を行います

コード利用の意味について

この目的は空白行など不要な行を削除するだけの処理です
なので、特別な処理は必要なく単純にIF分岐を利用して条件に一致する行を削除してしまえば良いだけに感じると思います

実行前の状態
処理実行前

この画像は処理を実行する前の状況の画像です
このシートのテーブルデータのうち、A列のセルが空白になっている行を削除したい処理になります

コードはうまくいかないコードなので、画像内で見にくいですが確認してください
流れとしてはA列の指定セル範囲をForEachループで回しています
その際にセルが空白であれば行全体を削除しています

空白行がうまく削除できていない状態
処理を実行後の状態

こちらの画像が処理を実際に実行したあとのモノです
一見して分かるように空白行が残ってしまっています

さらに確認してもらいたいのが、画像左下のイミディエイトに出力されたループ処理で実行されたセルのAddress文字列です

ForEachループではA2~A16までを代入して実行しているのに、A11までしかループが実行されていません

これはループ中に削除を行うことにより対象のセル数が減少していること
さらに削除した時点で、ForEachループの参照するセルが存在しなくなったことで自動的に次のセルを参照してしまっているため、連続した空白行の部分がうまく処理できていない状況です

これはそもそも仕様に対処する場合には、ループ処理を上からではなく下から行うことで対処することは出来ます
ですがその場合ForEachループは利用できなくなります

コード解説

記事コードの流れで処理を組むことで、目的の達成と処理速度の向上を図れます

記事コードを実行したあとのシート状態
コードを実行した後の状態

この画像は上記のコード実行前から記事コードを実行した後の状態の画像です
今回はしっかりと空白行が無くなっていることが分かると思います

ループ処理で実行されたセルのAddress一覧
ForEachループで参照されたセルAddress一覧

この画像はイミディエイトに出力されたものです
ForEachループで指定したセル全てがしっかり処理されていることが確認できると思います

Dim zzループ用セル As Range 'Forループ用のRange型変数
Dim zz対象セル As Range  '削除対象のセルの代入先のRange型変数

最初に使用する変数の宣言です
この処理ではRange型変数を2個使用します

1つはForEachループ用の変数です
もう1つは削除対象のセルを取得する変数です
ここに代入したセル範囲に対して削除を実行します

'指定セル範囲を全てループする
For Each zzループ用セル In Range("A2:A16")

~~中略~~

Next zzループ用セル

ForEachループの個所です
ここではとりあえずとして、セル範囲は決め打ちの処理になっています
実際にはワークシートのセル範囲であればUsedRangeやCurrentRegionのプロパティや画像のようなテーブル範囲であればListObjectsオブジェクトの各プロパティ等を利用してセル範囲を取得してください

'セルが空白なら削除対象とする
If zzループ用セル = "" Then

~~中略~~

Else: End If

削除条件を設定するIF分岐個所になります
ここの条件を変更することで削除対象を変更することが出来ます
逆に空白ではない行を削除したり、特定の値を対象とすることも出来ます

'対象セル変数が取得されているかで分岐
If zz対象セル Is Nothing Then
Set zz対象セル = zzループ用セル
Else
'取得されていればUnionメソッドを使用してセル範囲を追加
Set zz対象セル = Union(zz対象セル, zzループ用セル)
End If

削除条件に一致するセルであった場合は対象セル変数に代入させます
しかし、そのまま代入すると新しいセルで上書きされていってしまいますので、すでに取得対象が存在する場合は既存のセル範囲を残して追加する必要があります
そのためUnionメソッドを利用して、取得済み範囲に追加してきます

ただ、このメソッドは2つ以上のセル範囲を追加するメソッドのため
未取得のNothingではエラーとなります
そのため、最初に取得済みかどうかの判定を行ってから、取得済みでなければループ変数のセル範囲を代入して、取得済みであれば既存の範囲に追加します

この後で行全体選択のプロパティ等を利用するので、この時点の取得範囲はセル単体の追加でも問題はありません

'ループ終了後にセルが取得されているかを判定
If Not zz対象セル Is Nothing Then
'取得されていれば対象範囲を削除する
zz対象セル.EntireRow.Delete
'↓テーブル機能利用時はテーブルの列範囲内でなければエラーとなるので以下を利用する
'Intersect(zz対象セル.EntireRow, Range("A:D")).Delete
Set zz対象セル = Nothing
Else: End If

ここはループを抜けてきた後の処理になります
ループはあくまでも削除対象のセルを判定して取得を行うためのものになります
つまり、この時点で削除対象が必ず存在しているということは分かりません

ループを抜けてきた時点で対象セル変数がNothing(初期値)となっているかどうかで存在確認が出来ます
Nothingはオブジェクトなので、比較演算子はIsを利用する必要があります
この比較演算子は反対の意味の演算子が無いので、Notを利用して逆説にすることで、Nothingではない(対象セルが存在する)という条件が成り立つときに削除処理を実行します

取得した対象セルは単一セルなので、行全体を指定するためにEntireRowプロパティを利用して削除を実行します

ただここで問題がある場面がテーブル機能の場合になります
テーブル機能範囲の行を削除する場合に連続していない行全体を削除しようとすると画像の実行時エラーが発生します

テーブル機能の行を削除できないときのエラー
実行時エラーメッセージ

このエラーが発生して、Deleteメソッドが実行できません
その場合にはコメントアウトされているコードを利用してください

'↓テーブル機能利用時はテーブルの列範囲内でなければエラーとなるので以下を利用する
'Intersect(zz対象セル.EntireRow, Range("A:D")).Delete

こちらのコードはテーブル機能を利用している場合に利用するコードになります
テーブル機能の範囲を削除を行う場合には、テーブル機能の列範囲と同じ列範囲を指定する必要があります

ここでは決め打ちの列指定ですが、実際に使用する場合はDataBodyRangeプロパティ等を利用して列範囲を取得するようにするといいです

ちなみにこのセルを変数に代入させて、あとでまとめて一括で処理を行う方法は処理向上にとても貢献します
削除だけでなく、挿入やセルの書式設定等や一括入力などにも利用できます

レコードの新規追加(ADODB.Recordset)

AccessデータベースにExcelのデータをレコードとして新規追加するコード

'接続済みのデータベースのテーブルへのレコードセットの接続
zzDB_Rec.Open "テーブル名", zzDB_Con, 0, 3
’レコードの新規作成
zzDB_Rec.AddNew
’新規作成したレコードへの代入
zzDB_Rec!ID = ActiveCell
’レコードのデータベースへの更新
zzDB_Rec.Update
’レコードセットの接続を解除
zzDB_Rec.Close

データベースへのレコードの新規追加にはADOオブジェクトの「AddNew」メソッドを使用します
このメソッドを実行しただけでは既定値が入力されたレコードが作成されるだけになります
その追加したレコードに代入するにはADOオブジェクトのインデックス番号か、例コードのように列名を指定して代入します
代入後に「Update」メソッドを実行することでデータベースへの登録更新が完了します
操作後はADOオブジェクトの接続を解除します

レコードの新規追加といっても、実用で考えるとこの一連の手順が最低限必要になります

また、レコードの新規追加にはSQLのINSERT文も利用可能ですが、圧倒的にこちらのメソッドの方が高速なため、基本的にはレコードの追加はこちらのコードを利用します

メソッドの書式

引数(太字は必須引数)
AddNew FieldList, Values

このメソッドには引数が2つあり、どちらも省略可能です
この引数はそれぞれが対応しています

「FieldList」は列名を指定して、「Values」はその代入値の指定です
この指定は列名を文字列として指定して値を入力させます
この引数のイメージはSQLでのINSERT文の引数と同じイメージになります

また、この引数は配列を指定する必要があるためArray関数を使用して配列として指定する必要があります

zzDB_Rec.AddNew Array("ID", "日付"), Array(ActiveCell.Value, ActiveCell.Offset(0, 1).Value)

この様に引数の2つをどちらも指定して、かつ配列として指定する必要があります
こうすることで、例コードの様に1列ずつ代入せずに一括代入が出来ます

しかし、おおよそ一括代入は処理が高速になるはずなのですがこのメソッドはその通りでは無いようです
ほんの数回程度の検証しかしていませんが、10万件2列のデータの取得を行ったところメソッドの引数を使用した場合は1.4秒前後となりました
対して、各列ごとの取得では1.1秒前後となり、0.3秒もの差がつきました

CPU性能等に非常に影響があるので、数字に意味はありませんがコードの可読性含めて考慮するとほぼ利用する意味がありません
どの列に何を代入しているかは各列入力の方が分かりやすいです

実際の使用例

シートのテーブルに入力されたデータをデータベースに登録を行います
上記の引数説明にあるように、メソッドの引数を指定する方法は利用しません

コードの動きに関しても、データベースへの登録作業のため逐一の動きが確認できませんので結果のみを画像表示します

データベースへ登録するExcelのテーブルデータ
Excelのテーブル内容

この画像はExcelに入力されたテーブル内容のデータです
今回のコードはこの画像のデータをデータベースに登録を行います

レコードが未登録の状態
登録前のデータベース

自分の環境にはAccessがありませんので、データの確認としてExcelのナビゲーター画面を利用します
この画像の右範囲を確認してもらうと分かるように、まだデータベースにデータは何も登録されていませんので、空のテーブルであることが確認できます

データベースに登録された状態
登録後のデータベース

先に結果の画像を確認してみます
先ほどは空のテーブルであった部分にデータが入力されています

Nullというのはデータベースでデータが無い場合の値です
「””」は文字列の空白を意味するので、Null=””は成り立ちません

Excelのセルの空白はデータベースではNullが代入されます
これを防ぎたい場合は、空白の判定処理を入れる必要があります
Nullが許可されていない列の場合は実行時エラーが発生します

コード解説

Sub 処理テスト()


Dim zzDB_Con As Object
Set zzDB_Con = CreateObject("ADODB.Connection")
Dim zzDB_Rec As Object
Set zzDB_Rec = CreateObject("ADODB.Recordset")

'データベースへの接続開始
zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"
zzDB_Rec.Open "テーブル名", zzDB_Con, 0, 3

Do Until ActiveCell = ""
zzDB_Rec.AddNew
zzDB_Rec!ID = ActiveCell
zzDB_Rec!日付 = ActiveCell.Offset(0, 1)
zzDB_Rec!コード = ActiveCell.Offset(0, 2)
zzDB_Rec!金額 = ActiveCell.Offset(0, 3)
ActiveCell.Offset(1, 0).Select
zzDB_Rec.Update
Loop

zzDB_Rec.Close
Set zzDB_Rec = Nothing
zzDB_Con.Close
Set zzDB_Con = Nothing


End Sub

上の処理を実際におこなったコードです

Dim zzDB_Con As Object
Set zzDB_Con = CreateObject("ADODB.Connection")
Dim zzDB_Rec As Object
Set zzDB_Rec = CreateObject("ADODB.Recordset")

ここではデータベースへの接続のConnectionオブジェクトとRecordsetオブジェクトの作成を行っています
レコード操作を行う場合はRecordsetオブジェクトを利用します

'データベースへの接続開始
zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"

データベースへの接続を開始します
ここではAccdb形式のデータベースへの接続を行っています
Providerに関してはほぼ固定なので文字列をコピペしてもらって大丈夫です

zzDB_Rec.Open "テーブル名", zzDB_Con, 0, 3

ここでRecordsetオブジェクトを接続しています
引数の1つ目はテーブル名を文字列で指定します
2つ目の引数はデータベースへの接続を指定するので、前の手順で接続したConnectionオブジェクトを指定します

3つ目と4つ目はレコードを操作する際の方法の設定になります
詳細は別の記事にて行います
ここでは、レコードを前方移動で共有ロックをかけています
ロックに関しては「3」を「2」に変えても良いです

なお、この数値は参照設定を行えば定数が利用できます
参照設定を行っていないとエラーとなるので、数値を直接指定すると安定感があります

Do Until ActiveCell = ""
zzDB_Rec.AddNew
zzDB_Rec!ID = ActiveCell
zzDB_Rec!日付 = ActiveCell.Offset(0, 1)
zzDB_Rec!コード = ActiveCell.Offset(0, 2)
zzDB_Rec!金額 = ActiveCell.Offset(0, 3)
ActiveCell.Offset(1, 0).Select
zzDB_Rec.Update
Loop

ここでループ処理により空白セルになるまで行のデータを登録していっています
新規追加して、データを各列ごとに代入させています

zzDB_Rec!ID = ActiveCell

各列に代入する場合はRecordsetオブジェクトに「!」で列名を接続して指定することで列を限定できます

AddNewメソッドの引数だと1行に全てを一気に記載する必要がありますが、例の様に各列を指定できるので、こちらのほうが見やすいです
そのうえ速いなら選択の余地がないかもしれませんね

zzDB_Rec.Update

「Update」メソッドは更新した内容をデータベースに確定させるメソッドです
この時点でデータベースが更新されます

なお、AddNewメソッドはUpdateメソッドが実行されていないレコードがあった場合に、AddNewメソッドが実行されると自動的に更新確定を行います

zzDB_Rec.Close
Set zzDB_Rec = Nothing
zzDB_Con.Close
Set zzDB_Con = Nothing

最後にRecordsetオブジェクトの接続解除、データベースへの接続解除を行います
これはお決まりの流れです

以上の様にレコードは登録処理を行います
AddNewメソッドは非常に高速です
さらに、このメソッドを最速の環境にする方法としてデータベースを接続せずにテーブルをADOオブジェクト上に作成して、レコード登録が最も早いです
およそ、この記事での例コードの倍ほどの速さを発揮します
すこしテーブル作成がやっかいなのがネックです

テーブル内にアクティブセルがあるかを調べる

テーブルがアクティブ状態かどうかをアクティブセルの存在により判定するプロパティです

'テーブル内にアクティブセルがあればTrueを返す
Debug.Print Sheet2.ListObjects(1).Active

テーブルのアクティブ状態を取得するには「Active」プロパティを使用します

テーブル機能を利用したExcelにおいて、そのテーブル内にアクティブセルが存在するかを判定基準とするプロパティです
これは単純にテーブル自体のアクティブ状態を取得することで、アクティブセルが含まれているかを判定することが出来ます

リボンにテーブル機能がのタブが表示されていれば、アクティブ状態で表示されていなければ非アクティブ状態です
リファレンスにもありますが、テーブル範囲のオブジェクトにはアクティブにするメソッドがありません
そのため、テーブルをアクティブ状態にするには範囲内セルを選択状態にする必要がありますが、そのセルが本当にテーブル範囲なのかどうかはこのプロパティを利用しないと分からないということです

Excelはユーザーが簡単に変更可能であることと、テーブル機能自体が左上のセルがA1に確定しているわけでも無いし、シート内に複数のテーブルを作成することも可能です
行列を追加挿入されてもテーブル機能としては一切問題が無いのです
そのため、状況によってはこのプロパティを使用する必要が出てくることもあります

テーブル範囲でセル選択を変更したときのプロパティの取得の動き
プロパティの取得の動き

画像の動きを確認してください
最初に取得をしているB3セルは範囲内なのでTrueを返しています

そして、2つ目の取得では見出しのセルが選択された状態です
当然見出しもテーブル範囲なのでTrueが返されます
画像にはありませんが、総計行が選択されていてもTrueが返されます
データ範囲のみだけが対象ではない点には注意が必要です

3つ目の取得は範囲外なのでFalseが返されています

また4つ目の取得でも注意が必要です
このプロパティはあくまでもアクティブ状態の判定のために、テーブル範囲内にアクティブセルがあるかどうかを判定します
つまり、選択範囲全てがテーブル範囲内にあるとは限りません

実際このプロパティの使用範囲は狭いとは思いますが、シート内に複数のテーブルを作成しているときなどに利用してください