エクセルのマクロについてです。 作成者がやめてしまい、マクロができる人がおらず困っています。 画像の

エクセルのマクロについてです。
作成者がやめてしまい、マクロができる人がおらず困っています。
画像のようなエクセルのリスト(これはシート2とします。)を使用しており、別のシステムからコピペしたデータをシート1に貼り付けてシート3に必要な情報のみを抽出(VLOOKUP関数仕様)した表を作成し、シート2の表のセル(=シート3!B7の式をいれてます)に反映させて下の行にドラッグしています。
リスト上のマクロボタンは、B7を基準にそこからD列までなのかはたまたI列までなのかは不明ですが、昇順になるようになっています。
それで昇順にし終わったらシートを印刷するといったマクロが組まれていました。
今回のことは、マクロを組まなくても出来ることではあり、作成者がいた時は大量に件数が無かったので改ページ挿入をいちいちしておりましたが、ここ最近大量に来る日もあるので印刷範囲を可変にして印刷出来るようになったらいいネと話が出ました。
今回載せた画像のリストは10件までを表示して作成していますが、本物のリストは1500件くらいまで対応出来るようになっています。

ここで質問です。
印刷範囲を1ページ35行ずつで、最終ページだけシート3で抽出したデータがシート2に反映されてる最終行まで【最終ページだけ33行まで反映されてれば33行まで】＋対象件数をきちんとコピペしてるか通番を見て確認するための1行【対象総件数が500件なら1番最後の行の通番が501になってるかで確認するため】)にしたいです。
また、1500件分に対応するように作っており、抽出したデータが500件ならば1000件分反映されないセルが残ってしまいます。
この1000件のうちの1行は必要なので残りの999件は印刷範囲に入って欲しくない場合、どうやってマクロを組めばいいでしょうか？
可能なことなのでしょうか？
手動で修正した方がこのマクロを考えるよりも遥かに楽なのは承知なのですが、もし出来るのであれば教えていただきたいです。

おわかりになる方、無知なこの私にお知恵をお貸しください。
今組まれてるマクロの文章は画像の通りです。
塗りつぶしてるのはシート名で全て同一のシートです。
今回載せたエクセルのリストはマクロを修正したいExcelファイルと同一のものではなく、このようなリストだということをお見せするために私物のパソコンで作成したものです。
抽出日付の列は表示形式が日付になっています。他は標準になっていたと思います。

質問者からの補足コメント

• 画像見にくくて申し訳ございません。
  こちらに見やすいの載せておきます。

  
    補足日時：2019/04/09 23:45

• お礼を書いた後に気付いてまたお礼をしようとしたらお聞き出来なくなったので補足の方に書かせていただきます。
  貼り付けるデータの数なのですが、日によってばらつきがあり、30件の時もあれば500件などあります。(前日の集計を翌日抽出してリストを作成して利用してます。)
  なので、データの入ってる行の一番下の行までを最下行というと最下行についてはバラツキがあるのですが。。。

    補足日時：2019/04/10 08:39

• お返事ありがとうございます。
  分かりにくくて申し訳ございません。
  はい、仰られた通りのエラーが入っております。
  教えてくださった関数はエラーが表示されてるセルに入力すればいいのでしょうか？
  また、1ページの印刷範囲を35行にして最後の行だけ最終行＋1行にしたいのですが、
  1ページを35行にするのはどうやればいいでしょうか？
  質問ばかりで本当に申し訳ございません。

    補足日時：2019/04/10 11:36

• ifの関数を使用してシート2で反映するデータがなくてエラーの出てるセルは空欄にはなりました。
  その後、教えて頂いたマクロを入れてみたのですがエラーが出てしまいました。
  あと家のパソコンで試して見てるので印刷はせずに印刷範囲がきちんと出来てるか見たいので印刷プレビューに変えてます。
  私が無知すぎるが故に色々教えて頂いてるのにも関わらず出来なくて申し訳ございません。

  
    補足日時：2019/04/10 20:44

• コピペの内容はこれで完全OKでしょうか？

  
    補足日時：2019/04/10 23:11

• 今教えて頂いたマクロを入れて行の高さが大きかったのか小さくしたら通番35行ずつで出来ました！(入力件数は105件まで)
  かつ、空欄の行もはいってはいたのですが、1ページは通番35まで2ページは通番70まで
  3ページは通番105までで4ページ目で空欄の106行目が入ってました。
  この後、200番目まで試しに入れたら空欄の200番目まで6枚目にプレビューされてきてしまいました。(最下行は201番目に当たる空欄になってます。)
  シート2のB列からD列にシート3のデータが反映されてるとこまで+通番だけが入った空欄の直下1行だけが欲しかったのでこれだと通番だけが入っているだけの必要のない行が沢山印刷されてしまうのでこれは不要なのですが、印刷範囲に入らなくなるでしょうか？
  多分私が最初1ページ35行数指定とか言ってたからこうなったんですよね(すみません)

  
    補足日時：2019/04/11 00:10

No.12 回答者： yoreyore 回答日時： 2019/04/11 12:50

たぶん、他の方法(zongaiさんのアドバイス)を続けて検討されているので、私のNo.6、No.7の回答の方式はいらないのだろうとは思います。

　が、コメントがありましたので、補足させていただきます。

コメント：
シート1で貼り付けてるデータをVLOOKUP関数を使用してシート3に反映させてるので500件分のみしかデータがない場合、501件以降には#N/Aと表示されており、シート2はシート3に反映されてるデータをそのまんま引っ張ってきてるのでシート2の501件以降にも#N/Aと表示されております。
分かりにくくて申し訳ございません。

補足：
No.6の方式は、Sheet2には1500行目くらいまで(200行目でも構いませんが)、Sheet3のセルを参照して値を表示する式が存在し、そのSheet2のセルに書かれている式は一切変更しないことにしています。　No.6の方式は、Sheet2の印刷設定を指定するだけのものです。　No.6の方式を実行すると、Sheet2の中で、#N/Aと表示されているセルが数千あっても、その数千のセルの中で一番上の行にあるセルの直上の行(#N/Aと表示されているセルの一番上がB408ならば407行目、#N/Aと表示されているセルの一番上がB501ならば500行目)までを印刷し、その行よりも下(408行以下、501行以下)は印刷しないようになります。　そして、印刷は、各ページとも(タイトルの7行分とデータが35行分)セットで印刷されます。
つまり、(Sheet3にあるセルの式がVLOOKUPで検索できないと#N/Aのエラーになっているため)Sheet3を参照するSheet2にエラーが表示されていることを利用して、有効なデータのある行を判定しているのです。
もしも、印刷する各ページにはタイトルの7行分はいらない、データと通番号だけを35行/頁にしたいのであれば、No.6のコードを次のようにするだけです。

Sub MacroSample()
Dim rng As Range
On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not (rng Is Nothing) Then
r = rng.Row - 1
End If
Application.PrintCommunication = False
ActiveSheet.PageSetup.PrintArea = "$A$8:$D$" & r
ActiveSheet.PageSetup.PrintTitleRows = ""
ActiveSheet.ResetAllPageBreaks
For i = 43 To r Step 35
ActiveSheet.Rows(i).PageBreak = xlPageBreakManual
Next
Application.PrintCommunication = True
End Sub

No.11 回答者： zongai 回答日時： 2019/04/11 01:27

> 通番35行ずつで出来ました！(入力件数は105件まで)

ここは希望通りということですよね。

> かつ、空欄の行もはいってはいたのですが、1ページは通番35まで2ページは通番70まで
> 3ページは通番105までで4ページ目で空欄の106行目が入ってました。

データ数が105だから、106行目は4ページ目になるのは当然ですよね？
希望とは違うのですか？

> この後、200番目まで試しに入れたら空欄の200番目まで6枚目にプレビューされてきてしまいました。(最下行は201番目に当たる空欄になってます。)

6ページ目は176～210番目に当たるので、6ページ目のプレビューに入るのは当然ですよね。
データ数が200なので、プラス1行の201番めが追加されています。（おかしいの？）

通番ってシート2のA列に1500番まで入っているんじゃないのですか？
消す理由なんかないと思うのでその前提で作ってますが。

通番201が空欄なのは、あなたが空欄にしたのですか？

> シート2のB列からD列にシート3のデータが反映されてるとこまで+通番だけが入った空欄の直下1行だけが欲しかったのでこれだと通番だけが入っているだけの必要のない行が沢山印刷さ
>れてしまうのでこれは不要なのですが、印刷範囲に入らなくなるでしょうか？

印刷範囲外になるようにはしてるんですけどね。
200番までデータが入っているから画像のようになってるんじゃないの？

シート2のB列の空欄に見えてるセルって、何が入ってるんですか？
たとえばB207の中身とか。

-------------------------------------------------

教えて下さい、という投稿だったので、要所要所で説明も書いたりしてたんですが、
「動作がおかしい、直して」のオーダーになっちゃってますね。
急ぎ返事を書きたいからということでここは仕方ないとしても・・・

コピペうまくできてるか画像見て確認してくれなんて、それは自分でやることですよ。
ちょっと厚かましすぎです。

-------------------------------------------------
★気になったこと

①の投稿に添付されたA列は「リスト番号」
②補足で投稿されている印刷イメージのA列は「通番」

①のシートを出力するんじゃないの？
どこから「通番」という文字が出てきたの？
同じシートじゃないの？

★通番って、B列に書いてない？

No.2の回答で書いた内容を改めて書きます。
最下行取得をB列（2列目）にしているの理由は、
【1列目は通し番号で、情報入力のない1500番（？）まで入力されている】と思ったので、最下行取得に向かないと判断。
【2列目は顧客番号なので、情報入力のある場合は確実に入力されている】と判断したためです。

通番を200まで入れて、それを基準にページ区切りが入っているように思います。
つまり、通番がB列になっていると思うのですが、いかがですか？

情報入力がある場合、確実に入力のある項目はどれですか？
仮に「顧客番号」だとします。
「顧客番号」は何列目にかかれていますか？
通番が2列めなら、顧客番号は3列目でしょうか。
だとしたら、最終行を取得するのは3列目である必要があります。

matubi = Cells(Rows().Count, 【2】).End(xlUp).Row

この【】の部分の数字が3でなければなりません。（No.1で説明してます）
この【】は不要です、数字「3」だけ入れます。

この回答の最初に書いたことは後回しでいいです。
とりあえず、この最後に書いた、数字の書き換え「2」→「3」（顧客番号の列）をしてみて下さい。

No.10 回答者： zongai 回答日時： 2019/04/10 23:34

補足コメント（2019/04/10 23:11）拝見しました。

元からコピペし直したなら大丈夫だと思いますが…。
（最後の1行はご自身で追加されたのですよね）

その確認、回答者にさせます？(￣＿￣;）

ついでに実行結果も書いていただけると良かったんだけど。

この回答へのお礼

すいません、最後の1文以外でお願いします。

お礼日時：2019/04/11 00:13

No.9 回答者： zongai 回答日時： 2019/04/10 22:02

すみません、3行じゃなく、もう1行で

4行抜けです。

No.8 回答者： zongai 回答日時： 2019/04/10 21:41

他の方の回答もあるので、混乱されるかもしれませんが…

補足日時：2019/04/10 20:44
こちらは私の作った方のようなので、また回答投稿しますね。

画像にあるエラーは、コピペしてる内容が不十分だからです。
↓この頭に⬛を付けた3行が欠けています。

⬛'最下行を取得するよ――――
⬛matubi = Cells(Rows().Count, 2).End(xlUp).Row
⬛Do While WorksheetFunction.CountBlank(Cells(matubi, 2)) = 1
matubi = matubi - 1
Loop

余裕があれば、改めてコピペ等見直してみて下さい。

No.7 回答者： yoreyore 回答日時： 2019/04/10 18:00

＞　今日の件数は、50件くらいでしたので改ページプレビューで印刷範囲を指定したので500までは範囲指定されてはいません。

　いつも手動でやってるのでそのせいですね。

改ページプレビューでやってもいいのですが、だいたいが決まり切った様式で、意味のある有効行数が40,53,78,123,236,367,982,1357のようになるSheet2を、全部35行分/頁で印刷するor全部41行分/頁で印刷するという程度ならば、タイトル設定、改ページ設定をすれば、あとは、手動で[印刷]をすれば、全ページが連続して印刷されます。

＞　通し番号を見てきちんと抽出対象件数分あることを確認したいので、通し番号は欲しいのですが。。　通し番号アリでの方法が知りたいです。

3)現状では、Sheet2のA列は、8行目～1500行目まで1からの通し番号を入れてあるのでしょうか。
3)はい、そうです。

そうならば、
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$" & r
これで、A列の通し番号も印刷されます。
つまり、全ページに、通し番号付きで印刷されます。
なお、Sheet2のどこかのセルにエラーがある前提で｢MacroSample()｣はできていて、エラーの出てくる一番上の行の１つ上の行までで、印刷は終わります。エラーのあるセルの行を含む下の行は一切印刷されません。

4)現状では、Sheet2の[$B$8:$D$1500]あるいは[$B$8:$I$1500]に、Sheet3のセルを参照する式が入っているのでしょうか。
4)そうです！
5)現状では、Sheet3のデータ数が400件の場合、Sheet2のB407はきちんとデータが表示されていて、B408は、＃N/Aのように(Sheet3で参照してるセルの表示と同じ)になっているのでしょうか。
5)仰る通りです。

Sheet2のどこかには＃N/Aがでているハズですが、どこにも＃N/Aががないとなると、このコードのままだと、動作が狂います。

このコードは印刷設定をするだけなので、印刷は手動でやる前提にしてあります。紙をセットして、プリンターを選んで、プリンターのプロパティを設定しては、手動でやってください。　あとはexcelに戻って、Sheet2を表示させて、印刷をすれば、全ページが連続して印刷されます。

この回答へのお礼

度々申し訳ございません。
シート1で貼り付けてるデータをVLOOKUP関数を使用してシート3に反映させてるので500件分のみしかデータがない場合、501件以降には#N/Aと表示されており、シート2はシート3に反映されてるデータをそのまんま引っ張ってきてるのでシート2の501件以降にも#N/Aと表示されております。
分かりにくくて申し訳ございません。

お礼日時：2019/04/10 19:56

No.6 回答者： yoreyore 回答日時： 2019/04/10 12:17

脇から済みません。

１）現状は、Sheet2のページ設定のシートでタイトル行を[$1:$7]のようにされているのでしょうか。
2)現状では、Sheet2のページ設定の印刷範囲はどうなっているのでしょうか。
印刷しようとするときに、手動で印刷範囲を設定しているのでしょうか。
例えば、[A1:I501]のようにされているのでしょうか。
3)現状では、Sheet2のA列は、8行目～1500行目まで1からの通し番号を入れてあるのでしょうか。
4)現状では、Sheet2の[$B$8:$D$1500]あるいは[$B$8:$I$1500]に、Sheet3のセルを参照する式が入っているのでしょうか。
5)現状では、Sheet3のデータ数が400件の場合、Sheet2のB407はきちんとデータが表示されていて、B408は、＃N/Aのように(Sheet3で参照してるセルの表示と同じ)になっているのでしょうか。

もしも、3)4)5)がハイであって、【印刷は、各ページに1~7行目も印刷する】【最終ページは407行目(データの400件目)までで終わり、408行目以降はA列の遠し番号も印刷しない】ので構わないのであれば、次のようなコードでも、良いと思います。

Sub MacroSample()
Dim rng As Range
On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not (rng Is Nothing) Then
r = rng.Row - 1
End If
Application.PrintCommunication = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$" & r
ActiveSheet.PageSetup.PrintTitleRows = "$1:$7"
ActiveSheet.ResetAllPageBreaks
For i = 43 To r Step 35
ActiveSheet.Rows(i).PageBreak = xlPageBreakManual
Next
Application.PrintCommunication = True
End Sub

上のマクロを、Sheet2をアクティブにしておいてから実行すると、エラーのあるセルの1行上の行までを、Ａ列～I列の範囲で、各印刷ページの上部に1~7行目をタイトルとして印刷する設定になります。
For i = 43 To r Step 35　は、印刷改ページ設定で、実質35行毎の改ページ、
第一ページが（タイトル7行＋35行＝42行)なので、最初の改ページが43行目の上で、
(実質35行)毎で、エラーのある行(r)の上で終わり

あとは印刷をします。

この回答へのお礼

いえいえ、とても助かります！

１）現状、タイトル行の指定はなしでした

2)仰る通りです。
今日の件数は、50件くらいでしたので改ページプレビューで印刷範囲を指定したので500までは範囲指定されてはいません。
いつも手動でやってるのでそのせいですね。

3)はい、そうです。

4)そうです！

5)仰る通りです。

通し番号を見てきちんと抽出対象件数分あることを確認したいので、通し番号は欲しいのですが。。

本当に申し訳ございません。
でも見つけて回答して下さり、ありがとうございます！
もしお時間あるなら通し番号アリでの方法が知りたいです。

お礼日時：2019/04/10 14:52

No.5 回答者： zongai 回答日時： 2019/04/10 12:13

> 教えてくださった関数はエラーが表示されてるセルに入力すればいいのでしょうか？

エラーが入ってるセルだけに関数を入れるなら、はじめからエラー選択肢て削除したほうが早いですが。
私の回答は、シート2で「=シート3!B7」のように書いてある全セルをイメージしてました。

補足コメントされるのはいいのですが、そのエラーが何なのかも補足すべきですよ。
ついでに、そのエラーに関しての問題が解消されたのかどうかも含めて。


エラーの問題解消済みとして・・・
35行区切りというのも漠然としてるので、勝手なイメージで作ってみました。
とりあえず確認してみて下さい。
（印刷範囲も指定するようにしたので、行の非表示処理は無くしました。）


Sub 昇順と印刷()
'
' 昇順と印刷 Macro
'

'
'最下行を取得するよ――――
matubi = Cells(Rows().Count, 2).End(xlUp).Row
Do While WorksheetFunction.CountBlank(Cells(matubi, 2)) = 1
matubi = matubi - 1
Loop
'印刷範囲を初期化するよ――――
ActiveSheet.ResetAllPageBreaks
ActiveSheet.PageSetup.PrintArea = ""
'印刷範囲を設定するよ（7行目 項目行は全ページに印刷）
With ActiveSheet.PageSetup
.PrintArea = "$A$7:$I$" & matubi + 1
.PrintTitleRows = "$7:$7"
End With
i = 8: j = 0
'ページの区切りを入れるよ――――
Do While i <= matubi + 1
i = i + 35: j = j + 1
ActiveSheet.HPageBreaks.Add before:=Cells(i, 1)
Loop
'印刷するよ――――
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub

No.4 回答者： zongai 回答日時： 2019/04/10 10:58

> 500件分反映されたとして501件からは参照するデータがないためエラーが表示されてるのですが、このエラーになる部分は非表示にしてくれるというマクロなのでしょうか？

参照するデータが無いとエラーになるの？
どういうエラー？（「#N/A」？）参照元のシート3の時点でエラーになってるの？
（↑こういう情報も大事です）

このままだとNo.2の回答のは使えないかな・・・。

> シート2の表のセル(=シート3!B7の式をいれてます)に反映させて下の行にドラッグしています。

シート3のエラーを反映してきちゃっているなら、こういう式に置き換えるのもありかな。

=IF(ISERROR(シート３!B7),"",シート３!B7)

もし、シート3でエラーなら、空欄（””）。エラーでなければシート3の情報を反映。


質問や補足が、処理の流れだけ書いててわかりにくいです。
シート構成を明確に。
聞きたいことは完結に箇条書きに。
細かいことは更に補足すればわかりやすくなると思います。

例えば...

◼シート1　
別のシステムからデータをコピペ。
◼シート2
シート1の情報から必要な情報をVLOOKUP関数で抽出。
該当がない場合は「#N/A」になっています。（←こういう情報も）
◼シート3
印刷用のシート。
シート2の情報を「=シート3!B7」の様に直接参照しさせ、マクロで印刷させます。

★やりたいこと
データの最終行+1行までを印刷するようにしたい。

...とか。
細かく文章で書かれておられると思いますが、文章だけで書かれちゃうと把握し辛いです。

No.3 回答者： zongai 回答日時： 2019/04/10 10:23

印刷するシート2は、入力がない行は空欄ではなくてシート3を参照している関数が入っているから動作がうまくいかないってことかな。

matubi = Cells(Rows().Count, 2).End(xlUp).Row
Do While WorksheetFunction.CountBlank(Cells(matubi, 2)) = 1
matubi = matubi - 1
Loop
Rows(matubi + 2 & ":" & Rows().Count).EntireRow.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Rows(matubi + 2 & ":" & Rows().Count).EntireRow.Hidden = False

Do～Loopで、空欄行を考慮して最下行を取得するようにしてみました。

とりあえずこれでお試しを。
実際のシートの状態がわからないので噛み合わない部分もあるかもしれません。