Page Breaks
Otto Moehrbach
I'm trying to help a poster with re-arranging data as a function of page
break location. To do so I have to first locate the page breaks. I do so
with:
Dim pb As HPageBreak
For Each pb In Sheets("abc").HPageBreaks
pb.Location.Row
This creates an "...out of range" error because I don't have any page
breaks. So I add the statement:
ActiveWindow.SelectedSheets.PrintPreview
to first create the page breaks.
Question: How do I programmatically get out of Print Preview? Manually I
would click on the Close button. The macro recorder does not help with
this.
Follow-up question: Is there a better way to find the row numbers of the
page breaks?
Thanks for your help as always. Otto
Dave Peterson
ActiveWindow.SelectedSheets.PrintPreview
(Ugly solution, huh?)
--
Dave Peterson
ec3...@msn.com
Dick Kusleika
Create a dummy variable and use it store the count of the pagebreaks. It
appears that accessing that property creates the page breaks for your loop.
Dim x as Long
x = Sheets("abc").HPageBreaks.Count
--
Dick Kusleika
MVP - Excel
"Otto Moehrbach" <ot...@worldnet.att.net> wrote in message
news:eJU02Oe3...@TK2MSFTNGP12.phx.gbl...
Vasant Nanavati
Untested, but won't the Page Break Preview mode also initialize the page
breaks? You can toggle that programmatically without SendKeys.
Regards,
Vasant.
"Dave Peterson" <ec3...@msn.com> wrote in message
news:3E5D7BC7...@msn.com...
Vasant Nanavati
Regards,
Vasant.
"Dick Kusleika" <dicki...@home.com> wrote in message
news:#jGd$tg3CH...@TK2MSFTNGP11.phx.gbl...
Otto Moehrbach
Thanks to each of you for helping me. Here is what I found out. Look
at these 5 lines of code:
'SendKeys "%C"
'ActiveWindow.SelectedSheets.PrintPreview
'x = ActiveSheet.HPageBreaks.Count
'ActiveSheet.DisplayPageBreaks = True
For Each pb In ActiveSheet.HPageBreaks
1. Initially I used only the second line (ActiveWindow...) with the For
loop. This initialized the page breaks and the For loop recognized the page
breaks. But I couldn't get out of the Print Preview window. Dave's
solution (the first two lines together) gets me out of the Print Preview but
why? I don't know. Dave, could you shed some light on what your little code
does?
2. If I used only the "x=..." line or the "ActiveSheet.DisplayPageBreaks
= True" line, the page breaks would be initialized (visible on the screen)
but the For loop would not recognize them and a "....out of range." error
would result on the For loop line.
This initialization of the page breaks and the inability of the For loop to
recognize them is a mystery to me. Does anyone know why this would happen?
Thanks again for all your help. Otto
"Otto Moehrbach" <ot...@worldnet.att.net> wrote in message
news:eJU02Oe3...@TK2MSFTNGP12.phx.gbl...
Dick Kusleika
XL2000, Win98. I opened a new workbook, put a 1 in the cell A120 and ran
this code
Sub test()
Dim hpb As HPageBreak
Debug.Print ActiveSheet.HPageBreaks.Count
For Each hpb In ActiveSheet.HPageBreaks
Debug.Print hpb.Location.Address
Next hpb
Debug.Print ActiveSheet.HPageBreaks.Count
End Sub
And it gave me this result
2
$A$50
$A$99
2
So I'm not sure why it wouldn't work for you. What version are you using?
--
Dick Kusleika
MVP - Excel
Post all replies to the newsgroup.
"Otto Moehrbach" <ot...@worldnet.att.net> wrote in message
news:OcZ3qWm3...@TK2MSFTNGP10.phx.gbl...
Otto Moehrbach
I've got Excel 2002, Win XP. I will play with your code (in this
message) and see what happens. Thanks for your help. Otto
"Dick Kusleika" <di...@paragonconstructioninc.com> wrote in message
news:OCWawom3...@TK2MSFTNGP12.phx.gbl...
Otto Moehrbach
I have Excel 2002 & Win XP.
I'm running out of hair with this problem. I have had inconsistent
behavior with this code:
Sub FindPageBreaks()
Dim hpb As HPageBreak
Dim x As Long
Dim i As Long
x = ActiveSheet.HPageBreaks.Count
MsgBox "x is: " & x
For Each hpb In ActiveSheet.HPageBreaks
Next
MsgBox "i is: " & i
End Sub
I have gone round and round to establish some consistent behavior. I found
some but I can't explain it. Here is what I am doing:
New Workbook
One sheet
Put 1 in A120
Leave the active cell at A120
Run macro
It works (x=2, i=2, no error)
Insert new sheet
Put 1 in A120
Do End - Up arrow to A1
Run macro
x=2, Error "Subscript out of range" at the For line.
Same sheet
Do End - Down arrow to A120
Run macro
It works
Same sheet
Do End - Up arrow to A1
Run macro
It works!!!!!
Notice that the code didn't work the first time I set the active cell to A1
but did work in subsequent cycles.
Do you have any idea what is happening?? Otto
"Dick Kusleika" <di...@paragonconstructioninc.com> wrote in message
news:OCWawom3...@TK2MSFTNGP12.phx.gbl...
Dick Kusleika
This is curious. I get the same thing in 2000. See the commented lines in
the macro below for what I've tried.
Sub testpb()
Dim hpb As HPageBreak
'Dim OldCell As Range
'Set OldCell = ActiveCell
'Range("iv65536").Select
'Application.Goto Range("IV65536"), True
'OldCell.End(xlDown).Select
'Debug.Print Sheet1.HPageBreaks.Count
'OldCell.Select
'Debug.Print Sheet1.HPageBreaks.Count
'Debug.Print Sheet1.HPageBreaks(Sheet1.HPageBreaks.Count).Location.Address
If Sheet1.HPageBreaks.Count > 0 Then
Debug.Print Sheet1.HPageBreaks(1).Location.Address
End If
For Each hpb In Sheet1.HPageBreaks
Debug.Print hpb.Location.Address
Next hpb
End Sub
I thought maybe the activecell had something to do with it, so I made the
activecell the last cell, but got the same result. Then I tried using Goto
with the scroll, but no luck.
I noted in the Immediate Window, that when I got the subscript out of range
error, I could still type this
?Sheet1.HPageBreaks(1).Location.Address
so long as I had accessed the Count property. That seemed to put everything
right (why the Address property would but the Count property wouldn't, I
don't know). So I tried to print the address of the largest hpagebreak.
What I discovered is that while I could access the first page break, I
couldn't access the second. This, in the Immediate Window, gave me
subscript out of range
?Sheet1.HPageBreaks(Sheet1.HPageBreaks.Count).Location.Address
and so did this
?Sheet1.HPageBreaks(2).Location.Address
I resorted to testing the Count (and therefore accessing the property) and
if it was at least one, then accessing the location.address of the first
page break. That seemed to bring all the pagebreaks into VBA's view.
I haven't been able to break this one yet, so have at it and see if you can
break it.
Please post back with your results.
--
Dick Kusleika
MVP - Excel
Post all replies to the newsgroup.
"Otto Moehrbach" <ot...@worldnet.att.net> wrote in message
news:Ojba9#o3CHA...@TK2MSFTNGP11.phx.gbl...
Otto Moehrbach
You don't know much joy you brought me when you said you got the same
thing I did. I was starting to look under the bed for the boogeyman. I'll
work on it too, particularly what you said about accessing the first break
seeming to bring them all in VBA's view. Thanks again. Ottonews:uHhMwPp3...@TK2MSFTNGP10.phx.gbl...
Dick Kusleika
Keep me posted if you find anything new. Here or by email.
Thanks
Dick
"Otto Moehrbach" <ot...@worldnet.att.net> wrote in message
news:eeL3mbq...@TK2MSFTNGP12.phx.gbl...
Myrna Larson
initialized unless you scroll through the worksheet to the last one. I don't know if this has
been fixed in XL/XP. IN the mean time, you can use the old XL4 XLM macro functions to get the
number and location of the page breaks.
If you are interested, I have some lengthy code (purpose is to print the single page on which
the active cell lies) that uses the functions.
Dick Kusleika
I'd like to look at that code. If it's too lengthy to post here, please
email to dicki...@cox.net
Thanks
Dick
"Myrna Larson" <myrna...@charter.net> wrote in message
news:e9ft5v4nk3a8k1qo1...@4ax.com...
Dave Peterson
underscore. (It's the accelerator key).
So it's just "clicking" the close button.
--
Dave Peterson
ec3...@msn.com
Otto Moehrbach
Very interested. Please send it to me. Thanks. Otto
Otto Moehrbach
Exactly what do you mean by "scroll through the worksheet to the last
one"? I'm doing all this programmatically. Do you mean to simply GoTo the
end of the Used Area? Thanks for your help. Otto
Otto Moehrbach
Otto Moehrbach
Your Sendkey idea doesn't appear to work. I know I said it worked but
if you look at this entire thread you'll see that I have an erratic Excel
behavior problem. Look at this short macro:
Sub TestClose()
ActiveWindow.SelectedSheets.PrintPreview
SendKeys "%C"
End Sub
The Print Preview window comes up, as expected, but it stays there until I
manually either click on the Close key or I do Alt - C.
Do you have an idea why this is happening?
Thanks for your help. Otto
Otto Moehrbach
I tried Myrna's suggestion of "scrolling" through the PageBreak
collection and it appears to work.
Let me tell you what I have in my file for data first. I'm doing all
this to help a guy who posted a query to the NG.
He has a form that goes from A1 to J875.
He inputs, say, only 100 rows of data, always from the top down.
He wants to print his form.
He wants to print only the 100 rows plus the 6 rows at the very bottom of
the form, Rows 870:875.
One way is to hide all the other rows.
But he doesn't want the bottom 6 rows broken up over two printed pages.
This forces me to adjust the Rows.Hidden range as a function of the location
of the first two page breaks below his 100 rows of useful data.
Those two page breaks are easy to locate once VBA recognizes all the page
breaks.
Now to Myrna's idea. This macro works every time no matter where the active
cell is.
Sub FindPageBreaks()
Dim hpb As HPageBreak
Dim x As Long
Dim i As Long
x = ActiveSheet.HPageBreaks.Count
[A875].Select 'Myrna's idea of scrolling through the PB collection
MsgBox "Count is: " & x
For Each hpb In ActiveSheet.HPageBreaks
i = i + 1
Next
MsgBox "For loop count is: " & i
End Sub
I would appreciate it if you would exercise this idea on the ideas you've
been trying and verify that this does work. Thanks for all your help.
"Dick Kusleika" <di...@paragonconstructioninc.com> wrote in message
news:#hsDMoq3...@TK2MSFTNGP12.phx.gbl...
Dick Kusleika
I can confirm that works. I was so close to that in one of my previous
tests. I was selecting IV65536 and then reselecting the originally selected
cell before looping. If I would have kept the selection at IV65536 it would
have worked. So this fails
Dim hpb As HPageBreak
Dim x As Long
Dim i As Long
x = ActiveSheet.HPageBreaks.Count
[A875].Select 'Myrna's idea of scrolling through the PB collection
MsgBox "Count is: " & x
[A1].Select
For Each hpb In ActiveSheet.HPageBreaks
i = i + 1
Next
MsgBox "For loop count is: " & i
But if you get rid of [A1].Select, it works.
--
Dick Kusleika
MVP - Excel
Post all replies to the newsgroup.
"Otto Moehrbach" <ot...@worldnet.att.net> wrote in message
news:uNO8Dbz3...@TK2MSFTNGP11.phx.gbl...
Otto Moehrbach
Thanks for all your help. I've got all this well documented for next
time. Ottonews:OYTv5S13...@TK2MSFTNGP11.phx.gbl...
Dave Peterson
Sub TestClose()
SendKeys "%C"
ActiveWindow.SelectedSheets.PrintPreview
End Sub
(I reversed the order of the commands)
--
Dave Peterson
ec3...@msn.com