Excel（エクセル）活用コミュの可変の印刷範囲の設定について

印刷範囲の関数を下記の通り設定し、ファイルを保存しました。
ファイルを閉じるまではデータが入ると印刷範囲が可変しますが、
開きなおすと入力した関数が消えて、データ入力しても可変しなくなってしまい困ってます。
原因と解決策を教えてください。宜しくお願いいたします。

1.印刷範囲B:Nを指定。
　設定した印刷範囲には、Print_Areaという名前が自動的につく。
2.Ctrl＋F3 (又は、メニュー[挿入]-[名前]-[定義])
3.[名前]の Print_Area の [参照範囲]に、
　=OFFSET($B$1,0,0,COUNT($B:$B)+1,13) と入力。
4.閉じるをEnter
※4で保存するのですが、のちに確認すると
=OFFSET(Sheet1!$B$1,0,0,COUNT(Sheet1!$B:$B)+1,13) となってます。

COUNTについてですが、B1には No. と入っており、カウントされないので+1としています。
欲を言えば、B～Nまでの最終行を出し、その中で一番大きい数を入れたかったのですが、思いつかなかったのでCOUNTを使ってます。
B2～B10001に =IF(COUNTA(C2:N2)>0,1,"") ・・・・と入ってます。

原因に関係ないのかもしれませんが、BOOKを開いたときにヘッダー・フッターを指定してます。
Private Sub Workbook_Open()
　'LeftHeader ファイルの名 / シート見出し名
　　　ActiveSheet.PageSetup.LeftHeader = "&""Arial""&11" & "&F / &A"
　'RightHeader 現在の時刻 / 現在の日付 "&T / &D"
　　　ActiveSheet.PageSetup.RightHeader = "&""Arial""&11" & _
　　　　　　　　　　"Print: " & Format(Now, "MMM DD, YYYY") & " &T"
　'RightFooter ページ番号 / 全ページ数
　　　ActiveSheet.PageSetup.RightFooter = "&""Arial""&11" & "&P / &N"
End Sub

コメント(13)

最初
全て
最新の40件

[1] mixiユーザー 02月11日 10:05

ああっすみません。
環境は WinXP Pro Excel2007 です。
ファイル拡張子は .xls です。（Excel97-2003ブック）
皆様、よろしくお願いいたします。

[2] mixiユーザー 02月11日 10:32

Print_AreaをPrint_Area1としたら消えなくなりました…(^^;
自動で付く名前のままではいけないようです。

ただ、印刷範囲の点線が消えてしまいました。表示するようにできますか？

あと、C～Nまでの最終行を出し、その中で一番大きい数を出す関数は出来ますか？お教え下さい。よろしくお願いします。

[3] mixiユーザー 02月11日 10:49

末端のデータよりも下の罫線が消えてないので Print_Area1 としても駄目でした。やっぱり私には解決できません。
お知恵をお貸しくださいm(__)m

[4] mixiユーザー 02月11日 13:39

こんにちは。

>>0
> 原因に関係ないのかもしれませんが、BOOKを開いたときにヘッダー・フッターを指定してます。
それが原因かどうかはマクロを削除するか
マクロを無効にしてファイルを開けば確認できたのでは・・・
ちなみに、今回の現象はマクロが原因のようです。

詳しい理由はよくわかりませんが対策として、
以下の4行のコードを Workbook_Open() に追記するのはどうでしょう？

' ワークブックを特定せずに ActiveWorkbook のままでよいの？
ActiveWorkbook.Names.Add _
　　Name:="Print_Area", _
　　RefersTo:="=OFFSET(Sheet1!$B$1,0,0,COUNT(Sheet1!$B:$B)+1,13)"

>>2
> Print_AreaをPrint_Area1としたら消えなくなりました…(^^;
Print_AreaをPrint_Area1としても、Print_AreaもPrint_Area1も削除できました。

>>2
> ただ、印刷範囲の点線が消えてしまいました。表示するようにできますか？
再現できないのでわかりませんが、
１度「改ページプレビュー」か「印刷プレビュー」を行ってみてはどうでしょう。

>>2
> あと、C～Nまでの最終行を出し、その中で一番大きい数を出す関数は出来ますか？
以下の数式でなぜダメなのでしょう。
> B2～B10001に =IF(COUNTA(C2:N2)>0,1,"") ・・・・と入ってます。
C～Nまでが全て空白の行があるということですか？
そういうことであれば、
=IF(COUNTA(C2:N2)>0,row(),"") ・・・・
のように書き換えて max(b:b) というのはどうでしょう。

[5] mixiユーザー 02月11日 13:41

　↑
環境は WinXP Pro Excel2003 でした。

[6] mixiユーザー 02月11日 15:45

よっちゃんさん、いつも的確な回答ありがとうございます。
やはりマクロでしたか…そういえばヘッダフッタの指定をマクロに書き込んでから反映されなくなったような(^^;
マクロでヘッダフッタの指定をするとPrint_Areaも新たに設定されるようですね。名前変えたら自分の設定したものとPrint_Areaがありました。

>>以下の数式でなぜダメなのでしょう。
>>B2～B10001に =IF(COUNTA(C2:N2)>0,1,"")･･･
データの入力の仕方については通常は上から順に入れるのですが、一部のセルに空欄が出来てしまう場合があり、その数分、範囲が伸びません。(画像参照)
ただ、=IF(COUNTA(C2:N2)>0,row(),"") と max(b:b) で解決できそうですね。
やってみます。ありがとうございます。

原因がわかったので解決したのですが、「改ページプレビュー」「印刷プレビュー」いずれも試みましたがその時は点線が付きませんでした。

書き込みに気がつかなくて返事が遅れてしまいました。が、その間に下記のマクロを入れてみたら思いどおりの動きをしてくれました。…また突っ込みどころの多いコードかと思いますが。
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim c As Long, d As Long, e As Long, f As Long, g As Long, h As Long
Dim i As Long, j As Long, k As Long, l As Long, m As Long, n As Long
Dim maxRow As Long
c = Cells(Rows.Count, "C").End(xlUp).Row
d = Cells(Rows.Count, "D").End(xlUp).Row
e = Cells(Rows.Count, "E").End(xlUp).Row
f = Cells(Rows.Count, "F").End(xlUp).Row
g = Cells(Rows.Count, "G").End(xlUp).Row
h = Cells(Rows.Count, "H").End(xlUp).Row
i = Cells(Rows.Count, "I").End(xlUp).Row
j = Cells(Rows.Count, "J").End(xlUp).Row
k = Cells(Rows.Count, "K").End(xlUp).Row
l = Cells(Rows.Count, "L").End(xlUp).Row
m = Cells(Rows.Count, "M").End(xlUp).Row
n = Cells(Rows.Count, "N").End(xlUp).Row

maxRow = WorksheetFunction.Max(c, d, e, f, g, h, i, j, k, l, m, n)
ActiveSheet.PageSetup.PrintArea = "B1:N" & maxRow
End Sub

[7] mixiユーザー 02月11日 15:50

それから、
"Print: " & Format(Now, "MMM DD, YYYY") & " &T" は、
"Print: " & Format(Now, "MMM DD, YYYY hh:mm AM/PM") に直しました。
Now は日付と時間も取得できるのですね。

[8] mixiユーザー 02月11日 15:59

>>C～Nまでが全て空白の行があるということですか？
空白行ができる場合もあります。確定後に埋まります。

>>6
誤：一部のセルに空欄が出来てしまう場合があり、
正：一部、空白の行が出来てしまう場合があり、

[9] mixiユーザー 02月11日 22:48

> また突っ込みどころの多いコードかと思いますが。
いやぁ、ご勘弁をｗｗ
間違いじゃない限り基本的に人のコードには求められなければ口を出さないのですよ^^;

> Now は日付と時間も取得できるのですね。
VBAのNow()は標準関数のNOW()と同じですね。
日付だけの取得は Date で標準関数の TODAY() に相当します。

点線の件、興味あります。
もし宜しかったら点線が表示されない状態のファイルを 1@o123.jp に送ってみてくれますか？
※受信専用(転送用)に今適当に作った捨てメアドです。
※面倒や不都合ありましたら送ってくださらなくてもけっこうです。

[10] mixiユーザー 02月11日 23:48

いやあ、お恥ずかしい。
点線は消えたのではなく下に移動していただけでした

今までをまとめると、
ヘッダフッタをマクロで指定するとファイルオープン時にPrint_Areaが自動で設定され、前のPrint_Areaは上書きされてしまう。
ヘッダフッタまで指定したらPrint_Areaもマクロで指定しないとだめということですね。orz
お騒がせしてすみません。そして、ありがとうございました。

[11] mixiユーザー 02月11日 23:59

>>間違いじゃない限り基本的に人のコードには求められなければ口を出さないのですよ^^;
マクロは独学で勉強中なので、私個人は間違いでなくても指摘していただいた方が有難いのですが、これからはコードのスマート化も勉強していきたいと思います。

[12] mixiユーザー 02月12日 01:13

Goldenさん

> 点線は消えたのではなく下に移動していただけでした
了解です^^;

> 私個人は間違いでなくても指摘していただいた方が有難いのですが
こちらも了解です^^;
でも、「自分なりにこうやってできた！」という達成感が楽しいので
その気分を壊すようなことはしたくないのが本音でもあります。
実現できていればそれは紛れもなく正解だと思いますし、
コードってその人なりの色が出ていいもんですしね(変ですか？)

>>6のコードは、C:Nの範囲の最終行を取得するということでは最短なのではないかと思います。

でも、そこまで言われるのであれば何かコメントしてみます(汗)

私だったら、自分が使う用なら関係ないシートやセルの変更時に
今回の処理はする必要はないので無駄を省くという意味で
変数宣言のあとに以下の点線内のコードを追加するんじゃないかと思います。
---------------------------------------
'対象シートじゃなかったら Exit Sub する
If Not (Sh Is Worksheets(1)) Then Exit Sub

'対象セルが含まれていなかったら Exit Sub する
If Intersect(Target, Range("B:N")) Is Nothing Then Exit Sub
---------------------------------------

そもそも、シートモジュールの Worksheet_Change プロシージャに書いておけば
If Not (Sh Is Worksheets(1)) Then Exit Sub　すら不要になります。
　↑
現状は全てのワークシートが変更対象になっていますよね。
ワークブック内の全てのワークシートに処理が必要か、
もしくはワークブックにワークシートが１枚しかないという仕様を
考慮した上でのことであるならこのコメントは無視してください。

Intersect関数は、Target As Range を引数に受け取るプロシージャでよく使います。
ご参考までに^^;

[13] mixiユーザー 02月12日 01:55

よっちゃんさん
>>でも、「自分なりにこうやってできた！」という達成感が楽しいので
ごもっともです。

>>対象シートじゃなかったら Exit Sub する
>>対象セルが含まれていなかったら Exit Sub する
あ、