EXCEL VBAでHPageBreaks

質問

いつもお世話様です。
こちらで教えていただいたマクロでフッダーの前に自動で罫線を引こうとしています。
前の質問は→http://okweb.jp/kotaeru.php3?q=1310420

下記のマクロを動かすと、１ページだけの時はちゃんとフッダーの上に罫線が引けますが、2ページ目になると「インデックスが有効範囲にありません」という実行時エラーが出てしまいます。
どこがいけないのでしょうか？

Sub 自動罫線TEST()
Dim BreakSu As Integer
Dim BreakSu2 As Integer
Dim B As Integer
Dim Rw As Long
Dim LastRow As Long

For N = 1 To 3

With Cells
.ClearContents
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With

Range("A1:D" & N * 30) = N & N & N 'TESTデータ挿入

LastRow = Range("A65536").End(xlUp).Row '最終行取得
BreakSu = ActiveSheet.HPageBreaks.Count '改ページ数取得
Range(Cells(LastRow + 1, "A"), Cells(LastRow + 100, "A")) = "ABC" '改ページ数を増やすダミー
BreakSu2 = ActiveSheet.HPageBreaks.Count '増えた改ページ数取得

For B = 1 To BreakSu + 1
' MsgBox B & "-" & BreakSu + 1 & "-" & BreakSu2
Rw = ActiveSheet.HPageBreaks(B).Location.Row - 1 '改ページ前行取得（ここでエラー！）
With Range(Cells(Rw, "A"), Cells(Rw, "D")).Borders(xlEdgeBottom) '改ページ前罫線挿入
.LineStyle = xlContinuous
End With
Next B

Range(Cells(LastRow + 1, "A"), Cells(LastRow + 100, "A")) = ClearContents 'ダミー消去
ActiveSheet.PrintPreview

Next
End Sub

回答No.2

taocat · ベストアンサー

再度こんにちは。

姑息なコードを再アップしながらも納得いかず、
マイクロソフト技術情報を調べてみたところバグみたいですねぇ。

回避策として
追加した範囲の最後のセル（右下）を選択し、
Count,Location等を使用した後、最初のセルを選択

と書いてありましたので下記のようにしたらＯＫでした。
マイクロソフト、しっかりせんかいっ！(^^;;;

●のコードを追加。
-----------------------------------------------

Range(Cells(LastRow + 1, "A"), Cells(LastRow + 100, "A")) = "ABC"

●Range("A1").SpecialCells(xlCellTypeLastCell).Select

BreakSu2 = ActiveSheet.HPageBreaks.Count

For B = 1 To BreakSu + 1
Rw = ActiveSheet.HPageBreaks(B).Location.Row - 1
With Range(Cells(Rw, "A"), Cells(Rw, "D")).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
End With
Next B

　●　Range("A1").Select

Range(Cells(LastRow + 1, "A"), Cells(LastRow + 100, "A")).ClearContents ' = ClearContents
ActiveSheet.PrintPreview

Next
End Sub
---------------------------------------------

下記マイクロソフトサイトも覗くこと！
以上です。

参考URL：

http://support.microsoft.com/default.aspx?scid=kb;ja;210663

お礼

何度もありがとうございます！！！！

マイクロソフトのサイト見ました。
バグだったんですかあ・・・・。

でも、この回避方法だと、Application.ScreenUpdating = False　を使ってはいけないと書いてました。

質問に書いたコードはテスト用ですが本番は画面が動かないようにApplication.ScreenUpdating = Falseを入れるつもりだったんです。

でNo1で教えていただいた
ActiveWindow.View = xlPageBreakPreview '改ページプレビュー
ではそんな制限はないですよね？（実験しましたが大丈夫のようでした）

回答No.1

taocat

こんにちは。

では下記のように、●のコードを追加してみてください。
-------------------------------------------------

Range(Cells(LastRow + 1, "A"), Cells(LastRow + 100, "A")) = "ABC"

●ActiveWindow.View = xlPageBreakPreview　'改ページプレビュー

BreakSu2 = ActiveSheet.HPageBreaks.Count

For B = 1 To BreakSu + 1
' MsgBox B & "-" & BreakSu + 1 & "-" & BreakSu2
Rw = ActiveSheet.HPageBreaks(B).Location.Row - 1
With Range(Cells(Rw, "A"), Cells(Rw, "D")).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
End With
Next B

●　ActiveWindow.View = xlNormalView　　'改ページから標準へ

Range(Cells(LastRow + 1, "A"), Cells(LastRow + 100, "A")) = ClearContents
ActiveSheet.PrintPreview
Next
End Sub
-------------------------------------------

改ページプレビューを表示して強制的に改ページ位置を取得しています。

以上です。

お礼

taocatさまには本当にお世話になりました。
おかげさまで無事出来上がりました！

感謝感激です。
ありがとうございました。