エクセルのマクロに関してです。

1行毎に改ページにするマクロを設定しました。
マクロの内容は下記の通りです。
しかし、下記ですと、2つの問題があります。

【その1】
1行目と2行目はヘッダーにしているのに、1行毎に改ページだから1行目と2行目も2ページ分として印刷されてしまう。

【その2】
フィルターをかけているが、下記のマクロだと、フィルターで非表示にした行も印刷されてしまう。

何か良い方法はありますでしょうか。
また、フィルターで一部を非表示にするマクロも一緒にいれています。
何卒よろしくお願いいたします。

Private Sub Worksheet_Activate()

ActiveSheet.Range("$A$1:$A$200").AutoFilter Field:=1, Criteria1:="1"

With ActiveSheet
.ResetAllPageBreaks
For Each c In Range("A2:A500")
.HPageBreaks.Add Before:=c
Next
End With
End Sub

回答の条件
  • 1人1回まで
  • 登録:
  • 終了:2017/10/16 17:42:44
※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

ベストアンサー

id:kimuram No.1

回答回数27ベストアンサー獲得回数11

ポイント100pt

まず【その1】ですが、
1行目と2行目はヘッダーにしていることはこの2行は全ページの上部に印刷されることでしょうから、ここには改ページを設定しないこととすればよくて、


For Each c In Range("A2:A500")
              ↓
For Each c In Range("A4:A500")

と、対象行の範囲を変更すれば良いでしょう。

次に【その2】ですが、
これはやっかいな問題ですね。
フィルター機能とかで非表示としている中に改ページ制御が含まれていると、その数の分だけ、ページとしては印刷されてしまうものです。
(バグかどうかまではわかりませんが、昔に自分もなんだこれ!と、仕方なくあきらめたことを思い出しました。今でも、問題になってないのか、していないのか、Excel2016でも同じ状況のようです。)

で、考えたのですが、提案ですが、
1行ごとに改ページ設定する操作は、別途で後に行うことにするのです。
フィルター等で操作して印刷する段になってからマクロを起動し、表示されている行に対してだけ改ページ設定を行うのです。
そのためにマクロでは、行が表示状態なのか非表示状態なのかを判断して、非表示状態の行については改ページ設定を行わないようにします。

改造したマクロ例を以下に示しておきます。


Private Sub 行毎改ページ設定()
'フィルター等で行表示状態を変更した後に、印刷準備として実行する

' ActiveSheet.Range("$A$1:$A$200").AutoFilter Field:=1, Criteria1:="1"
' ↑(フィルター機能は別途で行う)

With ActiveSheet
.ResetAllPageBreaks
For Each c In Range("A4:A500")
If c.EntireRow.Hidden = True Then
'非表示の行なので何もしない
Else
'表示の行なので改ページ設定をする
.HPageBreaks.Add Before:=c
End If
Next
End With
End Sub

このマクロは標準モジュールに置いて、ワークシート上にボタンをマクロ起動用に設け、必要のときに起動操作したらよいでしょう。
(フィルターをかけるのは別途で、手動でも、あるいはWorksheet_Activateレベルのマクロでも構わないですが)

id:kimuram

後から考え直してみたのですが。
どうも思い違いもしていたようなので、再考しました。訂正させてください。
・1~2行をタイトル行とする(これは別途設定しておく)
・A列の3行目以降を値"1"である行のみをフィルターで表示制御する
・フィルターで非表示となる行のページは印刷対象とならないようにする
・当該シートをアクティブにするときに印刷制御のマクロを実行する

以上のような条件のもとでの運用としますと、次のような対応をすれば良いと思います。
※変更ポイントはコード中コメント(1)~(4)を参照してください。


Private Sub Worksheet_Activate()
'(1) フィルター状態のとき、いったん解除しておく
If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.AutoFilterMode = False
End If
'(2) 3行目からをデータとしてフィルターするので、Range範囲をA2からとする
ActiveSheet.Range("$A$2:$A$200").AutoFilter Field:=1, Criteria1:="1"

pagecnt = 0
With ActiveSheet
.ResetAllPageBreaks
'(3) 1~2行目はヘッダで、3行目からのデータ行でページ挿入の対象
For Each c In Range("A3:A500")
If c = "1" Then
'(4) 先頭ページを意識するためにページカウントしておく
' 先頭ページ目のデータのときは改ページ設定しない
pagecnt = pagecnt + 1
If pagecnt > 1 Then
.HPageBreaks.Add Before:=c
End If
End If
Next
End With
End Sub

2017/10/14 22:00:39
id:martytaka

できました!!!!!ありがとうございました!!!すごいです!!!

2017/10/16 17:57:11

その他の回答0件)

id:kimuram No.1

回答回数27ベストアンサー獲得回数11ここでベストアンサー

ポイント100pt

まず【その1】ですが、
1行目と2行目はヘッダーにしていることはこの2行は全ページの上部に印刷されることでしょうから、ここには改ページを設定しないこととすればよくて、


For Each c In Range("A2:A500")
              ↓
For Each c In Range("A4:A500")

と、対象行の範囲を変更すれば良いでしょう。

次に【その2】ですが、
これはやっかいな問題ですね。
フィルター機能とかで非表示としている中に改ページ制御が含まれていると、その数の分だけ、ページとしては印刷されてしまうものです。
(バグかどうかまではわかりませんが、昔に自分もなんだこれ!と、仕方なくあきらめたことを思い出しました。今でも、問題になってないのか、していないのか、Excel2016でも同じ状況のようです。)

で、考えたのですが、提案ですが、
1行ごとに改ページ設定する操作は、別途で後に行うことにするのです。
フィルター等で操作して印刷する段になってからマクロを起動し、表示されている行に対してだけ改ページ設定を行うのです。
そのためにマクロでは、行が表示状態なのか非表示状態なのかを判断して、非表示状態の行については改ページ設定を行わないようにします。

改造したマクロ例を以下に示しておきます。


Private Sub 行毎改ページ設定()
'フィルター等で行表示状態を変更した後に、印刷準備として実行する

' ActiveSheet.Range("$A$1:$A$200").AutoFilter Field:=1, Criteria1:="1"
' ↑(フィルター機能は別途で行う)

With ActiveSheet
.ResetAllPageBreaks
For Each c In Range("A4:A500")
If c.EntireRow.Hidden = True Then
'非表示の行なので何もしない
Else
'表示の行なので改ページ設定をする
.HPageBreaks.Add Before:=c
End If
Next
End With
End Sub

このマクロは標準モジュールに置いて、ワークシート上にボタンをマクロ起動用に設け、必要のときに起動操作したらよいでしょう。
(フィルターをかけるのは別途で、手動でも、あるいはWorksheet_Activateレベルのマクロでも構わないですが)

id:kimuram

後から考え直してみたのですが。
どうも思い違いもしていたようなので、再考しました。訂正させてください。
・1~2行をタイトル行とする(これは別途設定しておく)
・A列の3行目以降を値"1"である行のみをフィルターで表示制御する
・フィルターで非表示となる行のページは印刷対象とならないようにする
・当該シートをアクティブにするときに印刷制御のマクロを実行する

以上のような条件のもとでの運用としますと、次のような対応をすれば良いと思います。
※変更ポイントはコード中コメント(1)~(4)を参照してください。


Private Sub Worksheet_Activate()
'(1) フィルター状態のとき、いったん解除しておく
If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.AutoFilterMode = False
End If
'(2) 3行目からをデータとしてフィルターするので、Range範囲をA2からとする
ActiveSheet.Range("$A$2:$A$200").AutoFilter Field:=1, Criteria1:="1"

pagecnt = 0
With ActiveSheet
.ResetAllPageBreaks
'(3) 1~2行目はヘッダで、3行目からのデータ行でページ挿入の対象
For Each c In Range("A3:A500")
If c = "1" Then
'(4) 先頭ページを意識するためにページカウントしておく
' 先頭ページ目のデータのときは改ページ設定しない
pagecnt = pagecnt + 1
If pagecnt > 1 Then
.HPageBreaks.Add Before:=c
End If
End If
Next
End With
End Sub

2017/10/14 22:00:39
id:martytaka

できました!!!!!ありがとうございました!!!すごいです!!!

2017/10/16 17:57:11
id:martytaka

ありがとうございます。これから検証してみます。

その後、またお聞きすることもあるかもしれませんが

ご検討いただいたことに感謝を表しベストアンサーとさせていただきます。

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

回答リクエストを送信したユーザーはいません