エクセルに関しての質問

質問

エクセルの改ページプレビューで
現在の設定されている改ページ位置の
最終行の選択をするよい方法があれば教えてください。
数ページあるそれぞれの改ページの最終行をすべて一度に
選択できればいいのですが・・
よろしくお願いいたします。
実はその後、最終行に空白行を追加して印刷を
したいのですが、そちらの方は
最終行さえ選択できれば何とかなりそうなので（＾＾；）

回答No.1

Wendy02 · ベストアンサー

こんにちは。

ご質問の意味するものが良く分からない、というところなのです。

>数ページあるそれぞれの改ページの最終行をすべて一度に選択できればいいのですが・・

離れた場所ということでしょうか？

「それぞれ」ということは、1ページ目、2ページ目、それぞれに、改ページがありますね。（こういうのを自動改ページと呼びます）それを、一度に選択して、と言っても、処理しようがないと思います。

印刷範囲を設定して、点線を表示させるか、表示の改ページ・プレビューで表示して、切れ目をみるか、その程度にしかありません。

ページの設定で、物理的に紙の印刷範囲に余裕があるなら、空白行は、マージンを増やして調整すればよいのですが、空白行を挿入しても、次のページに回ってしまうだけで、ズレてしまいます。

補足

Wendy02様
すいません。
わかりにくいですよね。
うまく説明できないですが、
改ページ直後の行を選択して行の挿入をしても
確かに次のページのままですよね
改ページ位置を一つ下の行にずらせばすむことですが、
ページが多いもので一つ一つその作業をしていくのが
つらいのでなんとか自動でできないものかとご相談したのです。
何か良い方法があればよろしくお願いします。

回答No.4

pauNed

>try１のほうはエラーが出てきました
失礼しました。安易に考えてましたm(_ _)m

Sub try1改()
　　Dim r As Range
　　Dim n As Long
　　Dim i As Long

　　ActiveWindow.View = xlPageBreakPreview
　　With ActiveSheet.HPageBreaks
　　　　n = .Count
　　　　If n > 0 Then
　　　　　　Set r = Range(.Item(1).Location).Offset(-1)
　　　　　　If n > 1 Then
　　　　　　　　For i = 2 To n
　　　　　　　　　　Set r = Union(r, Range(.Item(i).Location).Offset(-1))
　　　　　　　　Next i
　　　　　　End If
　　　　End If
　　End With
　　r.Select
　　ActiveWindow.View = xlNormalView
　　Set r = Nothing
End Sub

ただ上記は本題にはあまり役に立たないので無視してください。
>でも残念ながら２ページ目以降には入りませんでした。
のリカバリとして、下記でいけるかもしれません。
残念ながら確信はありません。
うまくいかなかった場合、
Application.ScreenUpdating = False
の行を削除するといけるかもしれません。
ただ、うまくいったとしても、遅くて実用的でない懸念もあります。

Sub try4()
　　Dim i As Long

　　Application.ScreenUpdating = False
　　ActiveWindow.View = xlPageBreakPreview
　　With ActiveSheet
　　　　.UsedRange.Cells(.UsedRange.Count).Select
　　　　Do
　　　　　　i = i + 1
　　　　　　With .HPageBreaks(i)
　　　　　　　　If .Type = xlPageBreakManual Then
　　　　　　　　　　Rows(.Location.Row).Insert
　　　　　　　　Else
　　　　　　　　　　Rows(.Location.Row - 1).Insert
　　　　　　　　End If
　　　　　　End With
　　　　　　If i = .HPageBreaks.Count Then Exit Do
　　　　Loop
　　End With
　　ActiveWindow.View = xlNormalView
　　Application.ScreenUpdating = True
End Sub

もし、手動改ページがなく、各ページの行数が一緒なら、
ソートを使ったほうが速いでしょう。

Sub try5()
　　Dim cn As Long
　　Dim x　As Long
　　Dim n　As Long
　　Dim n1 As Long
　　Dim i　As Long
　　ReDim v(1 To 1) As Long

　　ActiveWindow.View = xlPageBreakPreview
　　With ActiveSheet
　　　　With .UsedRange
　　　　　　.Cells(.Count).Offset(, 1).Select
　　　　End With
　　　　cn = Selection.Column
　　　　x = Selection.Row
　　　　n1 = .HPageBreaks(1).Location.Row
　　　　n = .HPageBreaks(2).Location.Row - n1 - 1
　　　　ActiveWindow.View = xlNormalView
　　　　With .Cells(1, cn).Resize(x)
　　　　　　.Formula = "=Row()"
　　　　　　.Value = .Value
　　　　End With
　　　　v(1) = n1 - 2
　　　　i = 1
　　　　Do Until v(i) > x
　　　　　　i = i + 1
　　　　　　ReDim Preserve v(1 To i)
　　　　　　v(i) = v(i - 1) + n
　　　　Loop
　　　　.Cells(x + 1, cn).Resize(UBound(v) - 1).Value = Application.Transpose(v)
　　　　.Cells.Sort Key1:=.Cells(cn), _
　　　　　　　　　　Order1:=xlAscending, _
　　　　　　　　　　Header:=xlNo, _
　　　　　　　　　　OrderCustom:=1, _
　　　　　　　　　　MatchCase:=False, _
　　　　　　　　　　Orientation:=xlTopToBottom
　　　　.Columns(cn).Delete
　　End With
End Sub

ただ、この場合でも、罫線など書式が複雑だったら対応できないので
使えないかもしれません。
#中途半端なレスですみませんm(_ _)m

お礼

pauNed様
追加の回答をしていただいてたのですね
さっそく試させていただきました。
結果としては
try1改()はエラーがでてしまいました。
try4()はtry2()と同じくうまく空白行が挿入されました。
違いがよくわかりませんが^^；
try5()
は空白行は挿入されず改ページ位置が変わってしまいました。

try4()のときも残念ながら最終ページだけには
空白行は入りませんでした。

でも十分ですよ～～
ずいぶん作業が楽になります。
変な質問に丁寧に答えていただきありがとうございました。^^v

回答No.3

Wendy02

こんにちは。

#1の回答者です。

私の誤解があったのかもしれませんが、
>印刷前にページの最後にそれぞれのページの集計を入力する行がほしかったのです。

改ページ自体は、そんなに簡単には、いじれないのです。それをすると、全体がめちゃめちゃになっていきます。それで、今のレイアウトを壊さずに、その自動・手動改ページ（＝ページの終わり）の手前に一行挿入するという作業なのですね。

本来は、その挿入する行に、数式を入れるというのが一般の質問に出てくるマクロです。最近は、見かけなくなりましたが、最初は、その特殊なやり方を見よう見まねで覚えました。

普通、手作業では、ページの行数が決まっているなら、それを、計算で出して、それを、文字化して、名前－定義などで、行を選択するなどということをしますが、中には、ページの行数が決まっていないものもあるので、そんなに単純に行かないことがあります。数式で、ページの切れ目を出す方法もありますが、非常識なほどシートが重くなります。

それから、どのような表のスタイルになっているかにもよります。データはベタ打ちで繋がったものなのでしょうか。

もし、以下のマクロが通れば、その後に、数式などを入れるコードを書き入れますので、細かな内容を教えてください。ダメだったら別です。
------------------------------------------------------------------
Sub EnterEndofPage()
　 '
　　Dim i As Integer
　　Dim j As Integer
　　Dim k As Integer
　　Dim p As Long
　　Dim Ar() As Long
　　Dim uRng As Range

　　If ExecuteExcel4Macro("GET.DOCUMENT(50)") = 1 Then
　　　　MsgBox "1ページしかありません。", 64
　　　　Exit Sub
　　End If
　　k = ExecuteExcel4Macro("COLUMNS(GET.DOCUMENT(64))")
　　If IsError(k) Then
　　　　If WorksheetFunction.Count(ActiveSheet.Cells) = 0 Then
　　　　　　MsgBox "シートからページ数が取れません。", 48
　　　　　　Exit Sub
　　　　End If
　　End If
　　ReDim Ar(k - 1)
　　Application.ScreenUpdating = False
　　For i = 1 To k
　　　　p = ExecuteExcel4Macro("INDEX(GET.DOCUMENT(64),1," & i & ")")
　　　　Ar(i - 1) = p - i
　　Next i
　　For j = LBound(Ar()) To UBound(Ar())
　　　　If j = 0 Then
　　　　　　Set uRng = ActiveSheet.Rows(Ar(j))
　　　　Else
　　　　　　Set uRng = Union(uRng, ActiveSheet.Rows(Ar(j)))
　　　　End If
　　Next
　　'一括で挿入
　　uRng.Insert xlShiftDown
　　'本来は、この後に、数式を入れる作業があります。
　　Application.ScreenUpdating = True
'　　Set uRng = Nothing
End Sub

----------------------------------------------------
なお、これは、XLM関数方式で、この方法が一番、安定した改ページ行数を取れるからです。

お礼

Wendy02
いろいろありがとうございました。
解決できそうです。
お詳しいんですね^^

回答No.2

pauNed

こんにちは。
>数ページあるそれぞれの改ページの最終行をすべて一度に
>選択できればいいのですが・・

選択するだけなら

Sub try1()
　　Dim n As Long
　　Dim i As Long

　　ActiveWindow.View = xlPageBreakPreview
　　With ActiveSheet.HPageBreaks
　　　　n = .Count
　　　　ReDim v(1 To n)
　　　　For i = 1 To n
　　　　　　v(i) = .Item(i).Location
　　　　Next i
　　End With
　　Range(Join(v, ",")).Offset(-1).EntireRow.Select
　　ActiveWindow.View = xlNormalView
End Sub

...こんな感じですが、
>実はその後、最終行に空白行を追加して印刷を
>したいのですが、そちらの方は
>最終行さえ選択できれば何とかなりそうなので

これは、選択するとかえってややこしいです。
Wendy02さんもおっしゃってますが、改ページ位置がズレます。
また、その改ページが自動設定か、手動設定か、判断し、処理を分けなければいけません。

Sub try2()
　　Dim hp As HPageBreak

　　ActiveWindow.View = xlPageBreakPreview
　　For Each hp In ActiveSheet.HPageBreaks
　　　　If hp.Type = xlPageBreakManual Then
　　　　　　Rows(hp.Location.Row).Insert
　　　　Else
　　　　　　Rows(hp.Location.Row - 1).Insert
　　　　End If
　　Next hp
　　ActiveWindow.View = xlNormalView
End Sub

#環境によってはHPageBreaksがうまく取れない場合もあるかもしれません。
#その時はごめんなさい。

お礼

pauNed様
ごめんなさい
私の早とちりで2ページ目以降はできないような
ことを言ってしまいましたが
最終ページ以外はうまくいきました。
そのくらいなら大した手間ではありませんので^^
本当にありがとうございました。
助かりました。

補足

pauNed様
回答ありがとうございます。
さっそく試してみました
try１のほうはエラーが出てきましたので
try２のほうを試してみると
１ページ目の改ページの手前に見事に空白行が
挿入されていました。
質問が分かりにくくご迷惑をおかけしました。
やりたかったことができました。

印刷前にページの最後にそれぞれのページの
集計を入力する行がほしかったのです。

でも残念ながら２ページ目以降には入りませんでした。
VBAはよくわかっていないので、
私の知識では直せそうにありません・・・
できれば2ページ目以降にも空白行が入るように
ならないでしょうか？
わがまま言ってごめんなさい・・