Excel では行を新たに挿入することができます。
行全体を選択した状態で「右クリック→行の挿入」を選択すればできます（ショートカットキーを使うと「Ctrl + プラスキー」）。
新たに挿入された行には数字、文字、数式は何も入っていません。
行を挿入する目的によっては、上の行と同じ情報を入れたい場合がありますよね。
特に数式。
今日は、新たに行を挿入する場合に、上の行と同じ数式を入れ込むマクロ（Excel VBA）を紹介します。
Excel で作った現金出納帳を題材にしますね！
１、Excel 現金出納帳への行の挿入
Excel 現金出納帳
以前に Excel 現金出納帳の作り方を紹介しました。
| 会社や個人事業は、日々現金の取引があります。この現金の取引は従来は、入出金伝票に手書きで記帳した後、補助簿の現金出納帳に手書きで転記する記録方法が多くとられていました。今は直接会計ソフトに入力する人が多いと思います。また、Excelで作成した現金出納帳に記録したデータを、CSVファイルに加工して会計ソフトに取り込むというやり方もあります。今日は、まず伝票や現金出納帳への手書きの業務をやめたいという方に向けて、Excel で簡単に現金出納帳を作る方法を紹介します。計算に関数は使わず、足し算と引き算のみで作れ... 手書きをやめるための超簡単なExcel（エクセル）現金出納帳の作り方 - My タックスノロジー |
| アイキャッチ画像は、コメダ珈琲の期間限定メニュー ミニチョコノワールです（^^）さて、Excel で現金出納帳を簡単に作る方法について、こちらの記事↓で紹介しました。こんな感じ↓の現金出納帳ができあがっておりました。金額についてはD列に収入金額、E列に支払金額を入力し、F列に計算された差引残高が表示されます。上の記事の最後でも書いたように、この現金出納帳は見栄え上よくないところがあります。それはF列です。D列（収入金額）、E列（支払金額）へ入力がされていない場合でもF列に差引残高が表示されてしまうので、これを... Excel（エクセル）現金出納帳 印刷・PDF化したときの見栄えを良くする - My タックスノロジー |
| スプレッドシートは、Excel（エクセル）の Web版みたいなものです。Google アカウントさえあれば、Google ドライブの中で無料で使うことができます。Google ドライブへは Google のトップページからアクセスすることができます↓「新規」から「Google スプレッドシート」を選択します。するとこのような↓シートが開きます。パッと見 Excelとそっくりですよね。このスプレッドシートですが、使い勝手も Excel と似ています。さて、今日は Excel（エクセル）で作った現金出納帳をスプレッドシートに取り込んで共有して使う方法を紹介しま... Excel（エクセル）現金出納帳を Google スプレッドシートに取り込んで共有して使う... - My タックスノロジー |
シートをこのように↓ アレンジしたものです。
日にち、勘定科目、摘要、収入金額、支払金額を左から右に順に入力していきます。
差引残高はセルに数式を入れて計算しています。
出納帳の外の余白の部分（I列）に数式を埋め込んで差引残高を計算しています。
現金出納帳の一番上の期首残高の部分↓
期首残高の次の行以下は、同じ数式（行番号がずれていくだけ）を入れ込んであります。
そして、出納帳の差引残高の列（F 列）は見栄えの関係で「収入金額」「支払金額」のいずれかが入力されているときだけ、差引残高を表示するようにしてあります。
また、シート上で入力が必要な青色のセル以外は誤入力防止のため「シートの保護」をかけています。
入力に際し、TAB キーを押せばセルの選択を左から右側に移動できます。
一番右側（支払金額のセル）まできて TAB キーを押すと次の行に移ります。
さて、このような現金出納帳にある程度入力をしていくと、このように↓ なります。
行の挿入
さて、現金出納帳にある程度入力した後に、追加で入力したくなるケースがあると思います。
そんなときには行を挿入するわけですが、以下の操作で行える通常の行の挿入
右クリック→行の挿入（ショートカットキーは「Ctrl + プラスキー」）
これだと行は挿入できても、数式は入ってきません。
自分で入力しなければならなくなります。
行を挿入するたびに数式を自分で入れ込むのは大変なので、ここはマクロ（VBA）を使うべきですね。
仕組みを先にお話ししますと、
行のどこかを選択して、「1行挿入」ボタンをクリックすれば
上側に新しい行が挿入されて、かつ数式も入ってくるようにしてあります。
「1行挿入」ボタンにはマクロを埋め込んであります。
また、逆に行を削除したい場合には、削除したい行を選択して「1行削除」ボタンをクリックすれば削除できます。
「1行削除」ボタンにもマクロを埋め込んであります。
それぞれのボタンに埋め込んでマクロ
・1行挿入するマクロ
・1行削除するマクロ
について以下で説明しますね！
2、Excel 現金出納帳へ行を挿入するマクロの VBA コード
さて、まずは行を挿入するマクロの VBA コードから説明します。
「1行挿入」ボタンに組み込んであるマクロの分ですね。
全コード
全コードを以下に示します。
Row_Insert プロシージャに全て記述してあります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Sub Row_Insert() Dim Sheet_Name Sheet_Name = ActiveSheet.Name Worksheets(Sheet_Name).Unprotect Password:="123456" If Selection.Row >= 6 Then Rows(Selection.Row).Insert Range(Cells(Selection.Row - 1, 6), Cells(Selection.Row - 1, 9)).Copy Range(Cells(Selection.Row, 6), Cells(Selection.Row, 9)) Range(Cells(Selection.Row - 1, 6), Cells(Selection.Row - 1, 9)).Copy Range(Cells(Selection.Row + 1, 6), Cells(Selection.Row + 1, 9)) Else MsgBox "選択箇所では行の挿入はできません。" End If Application.CutCopyMode = False Worksheets(Sheet_Name).Protect Password:="123456" End Sub |
コード説明
コードについて説明します。
変数の定義
|
1 |
Dim Sheet_Name |
シートの名前を入れる変数「Sheet_Name」を定義しています。
シート名の指定
シート名を変数「Sheet_Name」に入れます。
ActiveSheet.Name は ActiveSheet オブジェクトの Name プロパティです。
アクティブなシートのシート名を意味します。
|
1 |
Sheet_Name = ActiveSheet.Name |
これ↑ で変数「Sheet_Name」にアクティブなシートのシート名を入れています。
シートの保護、保護の解除
シートの保護をしてあるので、行を挿入する際にはいったん保護を解除しなければなりません。
そして、行を挿入した後に再度シートの保護をします。
パスワードを「123456」と設定しているものとします。
シート保護解除は Unprotect メソッドを使います。
次のように記述します。
シート名は文字列で指定する場合は “” で囲います。
今回は変数で指定してあります。変数を “” で囲う必要はありません。
パスワードを指定するときは文字列を “” で囲います。
|
1 |
Worksheets(Sheet_Name).Unprotect Password:="123456" |
シート保護は Protect メソッドを使います。
次のように記述します。
シート名、パスワードについての必要事項は先の Unprotect メソッドと同じです。
行の挿入、上の行の特定の範囲をコピペ
行を挿入してその後に数式を入れ込む部分です。
数式の入れ込みは上の行の特定の範囲をコピペすることで対応しています。
図解すると、まず行を挿入します。
この部分は Insert メソッドを使っています。
次のように記述します。
今回のように、オブジェクトとして行全体を指定すると、その行の下側に新しい行が挿入されます。
オブジェクトである行全体は、 WorkSheet オブジェクトの Rows プロパティを使って指定します。
このように記述すると「行番号」に該当する行全体をオブジェクトとして指定することになります。
Rows(1) で1行目を意味します。
行番号を Selection.Row とすると、選択したセルの行番号を意味します。
ですので Rows(Selection.Row)とすることで、選択したセルと同じの行全体をオブジェクトとして指定しています。
そして
|
1 |
Rows(Selection.Row).Insert |
で選択したセルの上側の行に新しい行を挿入できます。
セルのコピー & ペースト
さて、新しい行を挿入しましたがそれだけではまだ不十分です。
・新たに挿入した行への数式の埋め込み
・ずれ込んだ行への数式の埋め込み
これらをやらなければなりません。
コピー&ペーストは以下のように記述して行います。
これで「範囲1」（セル）の内容をコピーして、「範囲2」（セル）に貼り付けます。
今回は、新たに挿入する行に１つ前の行の数式をコピペしたいわけです（普段の操作で言えば、セルを選択してコピペすれば数式も移ります）。
選択している行（Selection.Row）に新たに1行挿入されるので、その1行前（Selection.Row – 1）の内容の特定の範囲をコピペします。
セルで指定するときの列番号は F列〜I列（6 〜 9）とします。
|
1 |
Range(Cells(Selection.Row - 1, 6), Cells(Selection.Row - 1, 9)).Copy Range(Cells(Selection.Row, 6), Cells(Selection.Row, 9)) |
これ↑ で、挿入した新しい行の6 〜 9列目に、その1つ上の行の 6 〜 9列目をコピペします。
|
1 |
Range(Cells(Selection.Row - 1, 6), Cells(Selection.Row - 1, 9)).Copy Range(Cells(Selection.Row + 1, 6), Cells(Selection.Row + 1, 9)) |
これ↑ で、挿入した新しい行より1行下の6 〜 9列目に、挿入した新しい行より1つ上の行の 6 〜 9列目をコピペします（手作業でコピペするのと同じことになる）。
F列（6列目）、I列（9列目）に数式が入ってきます。
IF 文を使った場合分けで行を新たに挿入できる範囲を分ける
|
1 2 3 4 5 6 7 8 9 10 11 |
If Selection.Row >= 6 Then Rows(Selection.Row).Insert Range(Cells(Selection.Row - 1, 6), Cells(Selection.Row - 1, 9)).Copy Range(Cells(Selection.Row, 6), Cells(Selection.Row, 9)) Range(Cells(Selection.Row - 1, 6), Cells(Selection.Row - 1, 9)).Copy Range(Cells(Selection.Row + 1, 6), Cells(Selection.Row + 1, 9)) Else MsgBox "選択箇所では行の挿入はできません。" End If |
行を挿入しようとして選択しているセルの行番号が6以上の場合のみにマクロを実行するように IF 文を用いて場合分けしています。
（5行目以下の行は、それより上に入力用の行が無いから）
●選択している行の番号が6以上の場合、新たに行を挿入して数式をコピペ
●それ以外の場合「選択箇所では行の挿入はできません。」というメッセージを表示
このように↑しています。
コピーを解除
切り取り又はコピーの状態を解除するために、Application オブジェクトの CutCopyMode を False にします。
|
1 |
Application.CutCopyMode = False |
これで、コピーしている状態が解除されるのでコピーしている最中に出てくる点線を消すことができます。
3、Excel 現金出納帳の行を削除するマクロの VBA コード
次に、まずは行を削除するマクロの VBA コードから説明します。
「1行削除」ボタンに組み込んであるマクロの分ですね。
全コード
全コードを以下に示します。
Row_Delete プロシージャに全て記述してあります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Sub Row_Delete() Dim Sheet_Name Sheet_Name = ActiveSheet.Name Worksheets(Sheet_Name).Unprotect Password:="123456" If Selection.Row >= 6 Then Rows(Selection.Row).Delete Range(Cells(Selection.Row - 1, 6), Cells(Selection.Row - 1, 9)).Copy Range(Cells(Selection.Row, 6), Cells(Selection.Row, 9)) Else MsgBox "選択箇所では行の削除はできません。" End If Application.CutCopyMode = False Worksheets(Sheet_Name).Protect Password:="123456" End Sub |
コード説明
基本的には、行を挿入する場合とプログラミングの流れは変わりません。
1点行を削除する部分について説明します。
Delete メソッドを使っています。
次のように記述します。
今回のように、オブジェクトとして行全体を指定すると、その行が削除されます。
オブジェクトの指定の仕方は先ほどの Insert メソッドの場合と同じです。
|
1 |
Rows(Selection.Row).Delete |
で選択したセルの行が削除されます。
4、経理業務で使えるExcel現金出納帳プレゼント！！
2022年3月29日に追記致します！
上記でお話しした現金出納帳にマクロを組み込んでさらに使いやすくしたものをプレゼント致します！
▼▼以下よりダウンロードできますので、ぜひ有効活用して下さい▼▼
現金出納帳ダウンロード
なお、使い方につきましては以下の動画▼の中で解説しておりますので参考にして下さい。
5、まとめ
Excel 現金出納帳で行を挿入して上の行と同じ数式を入れるマクロ（VBA）を紹介しました。
行の挿入、範囲のコピペ という2つの作業の手間を省くことができます。
今回のようなシステムの中で特定のマクロを動かすためには、実行ボタンを作っておくと便利です。
Excel 現金出納帳の作り方についてはこちらの記事も参考にして下さい↓
| 会社や個人事業は、日々現金の取引があります。この現金の取引は従来は、入出金伝票に手書きで記帳した後、補助簿の現金出納帳に手書きで転記する記録方法が多くとられていました。今は直接会計ソフトに入力する人が多いと思います。また、Excelで作成した現金出納帳に記録したデータを、CSVファイルに加工して会計ソフトに取り込むというやり方もあります。今日は、まず伝票や現金出納帳への手書きの業務をやめたいという方に向けて、Excel で簡単に現金出納帳を作る方法を紹介します。計算に関数は使わず、足し算と引き算のみで作れ... 手書きをやめるための超簡単なExcel（エクセル）現金出納帳の作り方 - My タックスノロジー |
| アイキャッチ画像は、コメダ珈琲の期間限定メニュー ミニチョコノワールです（^^）さて、Excel で現金出納帳を簡単に作る方法について、こちらの記事↓で紹介しました。こんな感じ↓の現金出納帳ができあがっておりました。金額についてはD列に収入金額、E列に支払金額を入力し、F列に計算された差引残高が表示されます。上の記事の最後でも書いたように、この現金出納帳は見栄え上よくないところがあります。それはF列です。D列（収入金額）、E列（支払金額）へ入力がされていない場合でもF列に差引残高が表示されてしまうので、これを... Excel（エクセル）現金出納帳 印刷・PDF化したときの見栄えを良くする - My タックスノロジー |
| スプレッドシートは、Excel（エクセル）の Web版みたいなものです。Google アカウントさえあれば、Google ドライブの中で無料で使うことができます。Google ドライブへは Google のトップページからアクセスすることができます↓「新規」から「Google スプレッドシート」を選択します。するとこのような↓シートが開きます。パッと見 Excelとそっくりですよね。このスプレッドシートですが、使い勝手も Excel と似ています。さて、今日は Excel（エクセル）で作った現金出納帳をスプレッドシートに取り込んで共有して使う方法を紹介しま... Excel（エクセル）現金出納帳を Google スプレッドシートに取り込んで共有して使う... - My タックスノロジー |
| マクロ（Excel VBA）を組み込んだ Excel シートは自分1人が使うのであれば、自分だけが理解できる仕組みにすれば良いですが複数人が使うとなるとそういうわけにはいきません。Excel シートへの入力は、私個人はできる限りマウスを使わずにキーボードのみで入力を済ませたいと思っています。一方で、Excel を触り始めて間もない人からは、視覚的に分かりやすい入力方法の方が好まれます。今日は、マクロ（Excel VBA）のユーザーフォームにオプションボタンを設置して選択する仕組みを紹介します。Excel で作った現金出納帳での科目選択... Excel VBA のユーザーフォームのオプションボタンで Excel 現金出納帳の勘定科目を... - My タックスノロジー |