エクセル帳票で印刷範囲設定なしの一発清書印刷

vbaborder2eyecatch

エクセル伝票系テンプレート印刷範囲設定の便利な点と不便な点を比べてみると見えてくる。
印刷範囲設定を使わずに一発清書印刷できる方法をVBAを使って紹介します。

こんにちは　じゅんぱ店長　(＠junpa33)　です。

実務で、データを資料化してしかも体裁を整えた上で印刷する。

どのような職種の会社でも行っていることです。

この実務、当たり前の様に行いますが、実は、一つ間違えればムチャクチャ　業務パニック危険度が高く時間のかかる作業になってしまいます。印刷ミスなく。紙ロスなく熟している人はわずかでしょう。

エクセルで作成している書類は、Word（ワード）の代用として利用しているものも含めれば、数知れず多くあるでしょう。

書類作成不備で再印刷となる原因の中でも、「言い方間違い」や「漢字違い」など語彙力に関するものは、エクセル操作に起因するものではないので、別のアプローチで問題解決を行ってください。

今回のテーマは表計算機能を使って帳票書類を作った時の清書印刷を　” ビシッ ”　っと決める方法についてです。

例えば、とある部署では

事前に書面提出用の「テンプレート」を準備して、そこにその時の必要データをはめ込む。さらに印刷範囲設定でデータの入ったページ範囲をセットし印刷。完了。

これはある意味、” テンプレート命 ”　で、テンプレートが壊れるとすべてが死んでしまいます。何せテンプレートには印刷ページ50ページもの関数式が入っているのですから。

なんてことも現実あることです。

印刷範囲設定の功罪　エクセル書類印刷

vbaborder2004

よくご存じのように通常、書類印刷するのには、印刷したい部分だけ「印刷範囲設定」という設定をしてプリンター出力をします。

この印刷範囲設定が「ある意味スゴすぎて」要らない印刷安心感を振りまいています。

「印刷範囲設定をしてあるから大丈夫！プリントミスは極少です。」・・・本当ですかコレ

印刷範囲設定をしていたから、こうなった

テンプレートが載っているエクセルシートは清書用のものが多いでしょう。

そのテンプレートは、大量の行数に関数式や罫線が設定されているかもしれません。

まずこれが、功罪の1点目。

  • 　功：元データとリンクするだけで、立ちどころに書類が出来てしまいます。印刷したい部分を目視で確認しながら範囲指定すればそれで印刷準備完了です。
  • 　罪：印刷するつもりのない範囲のセルにまで、関数式や罫線が設定されているので、エクセルはそのセルをデータ入力セルとみなしています。印刷範囲設定をしない場合のデフォルト設定では、エクセルはそれも印刷対象に含めてしまいます。印刷範囲設定の変更をうっかり忘れてしまうと大変なことになります。

これは印刷範囲設定を設定せざるを得ないパターンになります。

功罪の2点目

印刷範囲設定を常にしていると、範囲外のセルに余計なデータも書き込んでいることが多いです。

  • 　功：印刷対象範囲外であれば、下書きスペースとして少々乱雑に使っても印刷されません。これは便利です。
  • 　罪：印刷設定を外してしまった時には、要らないデータの消し忘れで要らない部分も印刷されてしまいます。全く目も当てられないことになります。印刷範囲外に、「項目記入アドバイス」などの注記を書き込んでいる場合は要注意です。

功罪の3点目

印刷範囲を設定したからと言ってその範囲が全て1ページ内に収まるとは限りません。

  • 　功：入力セルサイズを変更しない限り期待通りに印刷してくれます。
  • 　罪：文字数が多くなると思ってセルサイズを大きくしてしまうと、印刷範囲内の右端列部分が途切れて別ページで印刷されてしまうことがあります。

功罪の4点目

印刷最終ページで、紙面途中で帳票が終了していることがある。

  • 　功：特に上げるものはありません。「初心者ッ！ぽさ」が発揮できることだけです。
  • 　罪：最終ページの下部余白が変に広く中途半端になる。対外的なものとするには「お粗末」に思える場合がある。初級者作成感が半端ない。

特にエクセルで作表した場合は、前ページ同様に、余白欄であっても末尾まで罫線を引いておきたい。

印刷範囲設定を考えない印刷の留意点

もし、印刷範囲設定をするかしないかを考えなくて、思うような清書印刷が出来たら、

これは非常に　” 楽チン ”　な作業になりますよね。

実際、逆の意味で、印刷範囲設定をしてしまったために起こるプリントミスも無くなるわけですから。

今回、例題として取り上げるのが、「請求書の帳票」ですが、印刷するための守るべき条件としては以下のようになります。

  1. 表題欄、相手先欄、発行者（自社）欄、総額欄のレイアウト変更禁止
  2. 列方向には改ページ区切り　幅はみ出し厳禁
  3. 最終ページの体裁を前ページと同じにする（ページの行数を合わせる）
目次へ戻る

伝票系のテンプレートを一発清書印刷するには

vbaborder2006

エクセルの基本機能として、

印刷範囲を設定してもしなくても、自動で改ページ区切りを表示してくれます。

プリントプレビューで見ると改ページの区切りは破線で表示されます。（印刷範囲は実線）

上下左右の余白の設定や行列のサイズ変更に合わせて自動で印刷範囲を表示しますので、この範囲内で書類作成すると手動で印刷範囲指定をしなくても、間違いなく1ページ内に収まるということになります。

一発印刷で清書書類を作るということですので、レイアウトを自動作成させることが必要になります。

そこで自動化するためにエクセルVBAプログラムを組み立てることになります。

プログラムを組み立てる上でポイントとなるところがいくつかありますので順番に説明していきます。

今回使うサンプル書類の紹介

まず、サンプルとして使う資料を紹介します。「請求書の帳票」を使って説明します。

vbaborder2001
サンプル写真１

＜設定条件＞

  • 列方向に対しては、改ページ区切りからはみ出さないことが前提です。
  • プリントサイズは「A4タテ」、余白設定は上(2cm)　下(1cm)　左(2cm)　右(1cm)
  • 1行目から14行目までは「相手先」と「請求金額」を除いてレイアウトは固定です（変更しません）。
  • 列幅はA列からG列を使用　H列とI列はクリアランス
  • 15行目以降、行方向は無制限の使用範囲とします。
  • 15行目以降の作表のための罫線は初期状態では設定していません。（可変するデータ行次第です。）

シートを見ながら手動で罫線を引き印刷してみる

ごく普通にデータの有るセル範囲を印刷範囲指定してプリントアウトしてみました。

今回は1ページに収まりましたが「以下余白」以下の空白スペースが奇妙な違和感になっています。

vbaborder2002
サンプル写真２
矢印下001

本来的にはこのような印刷書類にしたい訳です。

vbaborder2003a
サンプル写真３

シートを確認しながら手動で行う時は、一旦印刷される範囲を確認して、その範囲内のセルに罫線を設定することが必要です。（これは今までの書類印刷の方法と同様です。）

今回の目的は、サンプル写真１の状態から自動でサンプル写真３を作成することです。

サンプル書類を体裁よく自動で作成には

サンプル資料を印刷レベルで自動で清書書類に加工するためには・・・

印刷ページの列幅を管理（はみ出さない）すれば、色んなテンプレートに適応させることができます。

自動で清書書類を作成するPOINT

サンプル写真３のような書類を自動で作成できるようにするための押さえておくポイントはこのようになります。

サンプル書類の＜設定条件＞のほかに、VBAコード組み立てる上でのポイントとして重要なことは、

POINT 1

エクセルが自動で設定する改ページ位置をエクセルVBAで取得できるようにする必要がある。

ページ代わりは何行目かを調べます。

POINT 1の解決策

POINT 2

最終ページの改ページ位置はエクセルの仕様上取得することは出来ません。

例えば、全3ページならば２ページ目の先頭行と3ページ目の先頭行は取得できますが、4ページ目の先頭行（3ページ目の最終行に関係）は取得できません。

POINT 2の解決策

POINT3

作表する時にページを跨いだ場合は、見出し（プリントタイトル）を設定しておけば、跨いだページの表も1行目に見出しがついて、何の項目の値かを明確にすることが出来ます。

ただしこの見出し設定を行うと、新たに設置される毎に1行ずつ全体の行数が増えていきます。

POINT 3の解決策

POINT4

書類を作成するケース毎で、作表の最終行数が変動しますので、予め表に罫線が設定されていてもあまり意味を成しません。

できればその都度、罫線を設定する仕様にする方が合理的です。

POINT 4の解決策

POINT5

書類に掲載する計算表は、データを表計算させる方法として、テンプレートに埋め込まれた関数式ではなく、VBAコード上で計算を行い、結果をテンプレートのセルに配置する方法を取ることがベストです。

（関数式も値もスペースも入っていない空の状態を、セルのベースにしたい）

POINT 5の解決策

目次へ戻る

エクセルVBAコードを組み立てる

vbaborder2005

では実際にエクセルVBAのコードを組み立てる作業に移っていきます。

組み立てをする上での問題点やPOINTを解決しながら行っていきます。

作成押さえPOINTの解決策について考える

POINT 1の解決策

エクセルが自動で設定する改ページ区切りの位置（行列番号）を取得するのは少々厄介です。

  • 最初の1ページしかない書類印刷に対しては、（単純には）改ページ位置を取得することは不可能です。
  • 2ページ分ある印刷書類に対しては改ページ位置取得は1ページと2ページの間だけは取得できます。
  • 3ページ分ある印刷書類に対しては改ページ位置取得は1ページと2ページに加え、2ページと3ページの間だけは取得できます。

取得するためのVBAコードとしては、HPageBreaksとVPageBreaksオブジェクトで取得することになります。このHPageBreaksとVPageBreaksは、それぞれ行方向の区切り位置、列方向の区切り位置のオブジェクトになります。

したがって、

区切り位置の行番号（例えばNextRowとすると）次ページの最初の行番号を取得するコードは、

区切り行番号取得
  • NextRow = ActiveSheet.HPageBreaks.Location.Row

ということになります。

NextRowは次ページ先頭行番号ですので、前ページ最終行番号を知るためには「-1」をすることになります。

Point 1の解決コード

POINT 2の解決策

先ほども言いましたが、最終ページの改ページ区切り位置を、VBAコード上では知ることは出来ません。

また例題（サンプル写真２）のような場合は、1ページ目の最終行番号の等倍で2ページ目の最終行番号を知ることは出来ません。（2ページ目の表示行数で3ページ目の表示行数を知ることは出来ます。）

VBA裏ワザ　テクニック

VBAコード作成上、1ページ目の最終行番号と2ページ目の最終行番号をコードとして知る必要があります。

そこで、「VBA裏ワザ」として、

B列に新たにダミーデータを追加して、強制的に印刷ページが3ページになるようにします。

それによって、1ページ目の最終行番号と2ページ目の最終行番号を取得します。

取得完了後、B列に追加したダミーデータを消去し元の状態に戻すということをします。

Point 2の解決コード

POINT 3の解決策

普段からよく使われます「Print_Title（プリントタイトル）」ですが、

普段はあまり気にされませんが、これを設定することで、印刷の総行数が増えています。

ですので、

「POINT 2の解決策」を実行した後にプリントタイトルを設定してしまうと、せっかく取得した「ページの最終行番号」が全て変更されてしまいます。取得値と実際が合わなくなってしまいます。

印刷ページの最終行番号を取得する前に、プリントタイトルを設定しておくことが必要です。

VBAコード実行レベルで「POINT 3の解決策」を行ってから「POINT 2の解決策」を行うということです。

Point 3の解決コード

POINT 4の解決策

すでにあるテンプレートを利用して、今回のこのシステムを利用する場合ですが、

テンプレートにあるすでにひかれた罫線は邪魔になることが多くなります。

このシステムの中で、その都度必要部分だけ罫線を引くことが簡単に可能ですので、予め邪魔になるようなものは消しておいた方が安心です。

Point 4の解決コード

POINT 5の解決策

このブログの他の記事でもよく触れていますが、テンプレート内のセルに関数式を埋め込んでデータを計算させる方法は推奨していません。

セル内の関数式に一度でも不注意な変更が加わってしまえば、修正のための時間がとてつもなく必要になることがあるからです。また、関数式をメンテナンスしたい時でも、1セル修正して後はそのセルを上から下へ　” ナメて行けば ”　終わりなんて簡単なものだけではないからです。そんな苦い経験からVBAを始めた人も多いと思います。

これはもうエクセルVBAで計算を行うことが理想です。

全く何もない手つかずのエクセルシートに、コマンドボタン1クリックで、突然、完成された帳票・書類が現れる。

これほど爽快なことはありません。

改ページ区切りの行番号を取得するVBAコード

改ページ区切りの行番号を取得するVBAコードはこのようになります。

区切り取得コード
Sub 印刷最終行(ByRef KpRE As Long)
    Dim BRow, i, s, t As Long
    Dim KpR, KpRa, KpRb As Long
        BRow = Cells(Rows.Count, 2).End(xlUp).Row
'最終印刷ページの最終行取得のためのマーカー設定
        For s = 1 To 3
            Cells(BRow + s * 30, 2) = "☆"
        Next s
'改ページ区切りを取得する
        For i = 1 To ActiveSheet.HPageBreaks.Count
            With ActiveSheet
                KpR = .HPageBreaks(i).Location.Row - 1
                KpRa = .HPageBreaks(1).Location.Row - 1
                KpRb = .HPageBreaks(2).Location.Row - 1
            End With
'次ページの先頭行の指定2か所のセルにデータが無い場合はループを抜け出す。
            If Cells(KpR + 1, 2) = "" And Cells(KpR + 1, 3) = "" Then
                If i = 1 Or i = 2 Then
                    KpRE = KpR
                    Exit For
                End If
                Exit For
            End If
'2ページ目のページ行数を取得し最終ページの最終行を計算する
            KpRE = KpRb - KpRa + KpR
        Next i
'設置したマーカーを消す。
        For s = 1 To 3
            Cells(BRow + s * 30, 2) = ""
        Next s
End Sub

Point 1の解決コード

‘改ページ区切りを取得する
For i = 1 To ActiveSheet.HPageBreaks.Count
　With ActiveSheet
　　KpR = .HPageBreaks(i).Location.Row – 1
　　KpRa = .HPageBreaks(1).Location.Row – 1
　　KpRb = .HPageBreaks(2).Location.Row – 1
　End With

変数KpRで印刷書類内の改ページ区切りの行番号を全て取得します。

変数KpRaで最初の改ページ区切り（1ページと2ページの間）の行番号を取得します。

変数KpRbで2番目の改ページ区切り（2ページと3ページの間）の行番号を取得します。

vbawithstateeyecatch With～End Withの使い方。VBAコードを簡潔に記述する

Point 2の解決コード①

BRow = Cells(Rows.Count, 2).End(xlUp).Row

‘最終印刷ページの最終行取得のためのマーカー設定
For s = 1 To 3
　Cells(BRow + s * 30, 2) = “☆”
Next s

B列（品名欄）にダミーのマーカーとして「☆」をB列最終行（変数BRow）から30行分下に3回の位置で設置します。

印刷2ページ以降のページ当たりの印刷行数を調べるためのものです。

MEMO

お使いになる帳票によって、ダミーマーカーを設置する項目（列番号）を適宜選択してください。

vbalastcelleyecatch データ入力済セルの最終行番号を取得する

‘設置したマーカーを消す。
For s = 1 To 3
　Cells(BRow + s * 30, 2) = “”
Next s

ダミーマーカーを忘れずに最後に消しておきます。

Point 2の解決コード②

‘次ページの先頭行の指定2か所のセルにデータが無い場合はループを抜け出す。
　If Cells(KpR + 1, 2) = “” And Cells(KpR + 1, 3) = “” Then
　　If i = 1 Or i = 2 Then
　　　KpRE = KpR
　　　Exit For
　　End If
　　Exit For
　　End If
‘2ページ目のページ行数を取得し最終ページの最終行を計算する
　KpRE = KpRb – KpRa + KpR
Next i

改ページ区切り行番号を順に取り出していくループの中で、本当の最終改ページ区切りを知ることが必要になります。（ダミーデータを入れているので本物と偽物の判別が必要）

改ページ区切りが入った直下のセルにデータが入っているかどうかで判断していきます。

「品名欄」と「数量欄」いずれにもデータが入っていない場合は、ループから抜け出るように設定しています。

MEMO

どの項目が空欄になっているとループを抜け出すかは、使用する帳票で適時設定してください。

その抜け出た時の最後の改ページ区切りが最終ページの先頭ですので、そこから1ページ分の総行数を加えたものが最終印刷ページの最終行（罫線の下端）となります。

ページに罫線を引き清書印刷するVBAコード

清書印刷するVBAコードはこのようになります。

罫線設定行
Sub 罫線設定行()
    Dim KpRE As Long
    Dim i, UsR, UsC As Long
'前回の設定したプリントエリアとタイトルを解除する
        With ActiveSheet.PageSetup
            .PrintArea = False
            .PrintTitleRows = False
'プリントタイトルを設定する
            .PrintTitleRows = "$15:$15"
            .PrintTitleColumns = "$A:$G"
        End With
'セルを操作した範囲の一番右下のセル位置を取得する
        With ActiveSheet
            UsR = .UsedRange.Rows.Count
            UsC = .UsedRange.Columns.Count
        End With
'範囲内の罫線を削除する
        With Range(Cells(15, 1), Cells(UsR, UsC))
            .Borders.LineStyle = xlLineStyleNone
        End With
        Range("A1").Select
'印刷最終行プロシージャーを呼び出す
    Call 印刷最終行(KpRE)
'新たに罫線を設定する
'プリントエリアを設定できる（設定しなくてもOK）
'プリントプレビューを表示する
        Range("A15:G" & KpRE).Borders.LineStyle = xlContinuous
        With ActiveSheet
            .PageSetup.PrintArea = "A1:G" & KpRE
            .PrintPreview
        End With
End Sub

Point 3の解決コード

With ActiveSheet.PageSetup
　.PrintArea = False

‘プリントタイトルを設定する
　.PrintTitleRows = “$15:$15”
　.PrintTitleColumns = “$A:$G”
End With

プリントタイトルの設定は、印刷書類の総行数に影響しますので、このプログラム実行初期時点で設定コードを記述しておきます。

MEMO

帳票のプリントタイトル（見出し位置）は適宜変更してください。

Point 4の解決コード

‘セルを操作した範囲の一番右下のセル位置を取得する
With ActiveSheet
　UsR = .UsedRange.Rows.Count
　UsC = .UsedRange.Columns.Count
End With
‘範囲内の罫線を削除する
With Range(Cells(15, 1), Cells(UsR, UsC))
.　Borders.LineStyle = xlLineStyleNone
End With
Range(“A1”).Select

再使用のテンプレートで使用する場合は、先回使用時の罫線が設定されたままになっていることがあるので、先に使用されたセル範囲内の罫線を全て消去します。（書類タイトルや宛先・発行者情報は範囲外設定をします。）

変数UsRは、使用セルの最終行。変数UsCは使用セルの最終列になります。

MEMO

このサンプルでは、Cells(15，1)のセル位置が帳票の作表の始まりになっていますが、

お使いになる帳票での始まりのセル位置を適時指定してください。

Point 項目以外の解決コード

‘前回の設定したプリントエリアとタイトルを解除する
With ActiveSheet.PageSetup
.PrintArea = False
.PrintTitleRows = False

先回の使用で設定したプリントエリアとタイトルを最初に解除しておきます。

vbacleareyecatch シートクリアーを目的のメソッド別にVBA最速理解

‘印刷最終行プロシージャーを呼び出す
　Call 印刷最終行(KpRE)

変数KpREを使うために別のプロシージャー「印刷最終行」を呼び出します。

vbacalleyecatch 部品化プロシージャーでCallステートメントは必須

‘新たに罫線を設定する
‘プリントエリアを設定できる（設定しなくてもOK）
‘プリントプレビューを表示する
　　Range(“A15:G” & KpRE).Borders.LineStyle = xlContinuous
　　With ActiveSheet
　　　.PageSetup.PrintArea = “A1:G” & KpRE
　　　.PrintPreview
　　End With

罫線を設定します。

プリントエリアは、設定しても大丈夫です。不要な場合は、コメントアウトさせてください。

印刷前状況としてプリントプレビューを表示させます。確認してプリントボタンを押して印刷実行になります。

vbaborder1eyecatch Bordersの/位置/線種/太さ/色/と<外枠だけの罫線>の設置
目次へ戻る

印刷範囲設定しないで清書印刷のまとめ

vbaborder2007

エクセルシートを印刷するときには「印刷範囲設定」を使うのはほぼ当たり前の人が多いでしょう。

これが逆に、デフォルトではどれ位のセル範囲なら1ページに収まるのか等の感覚を鈍らしてしまいます。

今回の記事の様に、エクセルのデフォルト機能を上手く使っていけば、プリント範囲の途中切れや、不要部分の余計な印刷ミスなどを削減することも可能です。

悩みの多い清書プリント作業を少しでも簡潔に上手くやれるように、業務改善していきましょう。

Point 5の解決

安全に書類を作成完了させるためには、最終的には、シートのセルには関数式を埋め込まないことです。

「シートのセルは至極簡単に記述内容を変更できる」ことは利用利便性の根本ですが、

と同時に、いたって簡単に改変できてしまうことは、デメリットの最大極地ににもなっています。

エクセルシートのセルには、最終結果（文字・文章・数値）のみを表示させるだけ。

そしてさらに、同じ内容を何度でも再表示させることが出来る。

このような安全対策を実行させることが出来るのは、エクセルVBAしかないと言っても過言ではありません。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう！

エクセルVBA業務ツールで日常の業務改善を行いましょう。

＜記事内容についての告知＞

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。

目次へ戻る

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

af_banner01

Dstyle web

dstyleweb_logo
dstyle_320x50-min

前の記事

vbaabspatheyecatch
絶対パスの全階層のパスを変数化できるVBA

次の記事

エクセルVBAでつくる物品購入申請書テンプレート
buppinsinseieyecatch