Excelでの印刷範囲を自動で設定したい

Excelで注文書を作っています。
毎日1～64品目を50か所ほどに注文していて、取引先毎品目数は毎日変動します。
取引先毎に注文書はシートで別れています。

B2からF10までのセルはタイトルや日付などを入力しています。
B12～C12に品名備考、D12に価格、E12～F12までは備考を入力していきます。
品名、価格の全部のセルには他のexcelブックからの在庫価格等データを呼び出すためのVLOOKUPの計算式が入っていて品名、価格は自動で入力されます。
VLOOLUPで該当しなかったら空欄と表示されるよう設定されています

用紙はA4サイズで1ページ32品目印刷できるように高さを調整しています

33品目以降がある場合に備えて、印刷範囲は80行目までとして2ページ目まで印刷しています
そのためセルが空欄でも罫線だけ印刷されてしまっています

これらを解消するために品名が入っている行までだけを印刷範囲として罫線だけのセルは印刷範囲から除外したいです


画像は仮のデータですが形はこのままです
B15～F80は空欄でも印刷してしまっています

この画像の場合、B2～F14までを印刷範囲とするか、
もしくは44行目以降に商品名がある場合だけ2ページ目を印刷するように設定したいです

何かいい方法はないでしょうか

No.3 ベストアンサー 回答者： tom04 回答日時： 2020/09/11 09:43

続けてお邪魔します。

＞注文が0の場合のシートは　B12,C12,D12,E12　セルに　#N/A　と表示されます

こちらを解消するのが先決ではないでしょうか？
極力エラーを出さない数式にすることをおススメします。

実際どのような数式が入っているのかは不明ですが・・・
=IFERROR("VLOOKUP関数","")

といった感じで・・・

※　別案として、マクロを使わず条件付き書式で罫線を設定してみてはどうでしょうか？

B12セル以降の罫線はすべて消去しておき
B12～F列の適当な行（多めに）範囲を選択　→　条件付き書式　→　新しいルール　→　数式を使用して・・・　→　数式欄に
=$B12<>""
とし　→　書式　→　罫線タブ　→　好みの罫線を決める！

これでB列にデータがある行のF列までに設定した罫線が入り、その行までが印刷範囲になるはずです。m(_ _)m

この回答へのお礼

=IFERRORを利用することによって、期待した印刷範囲の指定ができるようになりました！
また、条件付き書式でも指定できるようになり、いろいろな状況で使い分けたいと思います。

何度も答えていただき、ほんとうにありがとうございました！！

お礼日時：2020/09/11 17:53

No.2 回答者： tom04 回答日時： 2020/09/10 21:24

No.1です。

B列には数式が入っているのですね。
前回のコードはすべて消去し、↓のコードにしてみてください。

Sub Sample2()
　Dim i As Long
　　For i = Cells(Rows.Count, "B").End(xlUp).Row To 12 Step -1
　　　If Cells(i, "B") <> "" Then Exit For
　　Next i
　　　If i > 11 Then
　　　　ActiveSheet.PageSetup.PrintArea = "$B$2:$F" & i
　　　Else
　　　　MsgBox "印刷データなし"
　　　End If
End Sub

今度はどうでしょうか？m(_ _)m

この回答へのお礼

まさに望んでいたとおりの範囲指定ができました！

ただ、質問時に間違えて書いてしまったのですが、注文先によっては注文が0の場合がありそのシートにこのマクロを実行したところ
「型が一致しません。」と表示され実行時エラーが出てしまいました。
注文が0の場合のシートは　B12,C12,D12,E12　セルに　#N/A　と表示されます。それより下のすべてのセルは数式が入った空白に見えるセルとなります。

このような場合にエラーを出さないようにするには、どのようにしたらよいでしょうか。

お礼日時：2020/09/10 22:19

No.1 回答者： tom04 回答日時： 2020/09/10 20:07

こんばんは！

VBAになりますが、一例です。
とりあえず、B2～F列最終行までを印刷範囲としました。

Sub Sample1()
　Dim lastRow As Long
　　lastRow = Cells(Rows.Count, "B").End(xlUp).Row
　　　ActiveSheet.PageSetup.PrintArea = "$B$2:$F" & lastRow
End Sub

※　B列で最終行を取得しています。
印刷する前にマクロを実行し、印刷プレビューで確認してみてください。

※　B列に数式が入っていて空白に見えるセルがあると、そこも印刷範囲になってしまいます。m(_ _)m

この回答へのお礼

ありがとうございます。

さっそくプレビューで確認してみたところ
備考に書いてくださったように、商品、価格等のB,C,D,E,F列はVLOOKUPの数式が入っていて空白に見えるセルだったため、すべてが印刷範囲になってしました。
数式は無視できるような方法があればよいのですが…

お礼日時：2020/09/10 21:09