Deleting HPageBreaks returns...

[darlove]

Hi,

I have just read a quite lengthy conversation on the subject of
removing horizonal and vertical page breaks. Unfortunately, the person
who was hoping to find a solution to the problem here did not find it
then. Maybe today she knows how to achieve the result she wanted to.
The problem is the following. I want to programatically remove all (or
some) horizontal page breaks on a sheet. One suggestion that I've found
here is to type in this piece of code and set in motion:

Sub DeleteHPageBreaks()

Dim pb As HPageBreak
Dim lCount As Long

For lCount = ActiveSheet.HPageBreaks.Count To 1 Step -1
Set pb = ActiveSheet.HPageBreaks(lCount)
If pb.Type = xlPageBreakManual Then pb.Delete
Next lCount

End Sub

Regrettably, it produces an error (1004) which does not say much about
what's really happened. Actually, says nothing at all. I have also
tried a For Next...Loop version of the above with the same result. Is
this a real bug? Could anybody, please, give me an answer?

Darlove (PL)

 

[Jim Cone]

Cells.PageBreak = xlNone will remove all of them.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"darlove" <[email protected]>
wrote in message
Hi,
I have just read a quite lengthy conversation on the subject of
removing horizonal and vertical page breaks. Unfortunately, the person
who was hoping to find a solution to the problem here did not find it
then. Maybe today she knows how to achieve the result she wanted to.
The problem is the following. I want to programatically remove all (or
some) horizontal page breaks on a sheet. One suggestion that I've found
here is to type in this piece of code and set in motion:

Sub DeleteHPageBreaks()
Dim pb As HPageBreak
Dim lCount As Long
For lCount = ActiveSheet.HPageBreaks.Count To 1 Step -1
Set pb = ActiveSheet.HPageBreaks(lCount)
If pb.Type = xlPageBreakManual Then pb.Delete
Next lCount
End Sub

Regrettably, it produces an error (1004) which does not say much about
what's really happened. Actually, says nothing at all. I have also
tried a For Next...Loop version of the above with the same result. Is
this a real bug? Could anybody, please, give me an answer?

Darlove (PL)

 

[darlove]

Thanx Jim.

It works but to my surprise only in this form, that is with Cells. When
I wanted to do the trick with a continuous range, for example

[header].EntireRow.Cells.PageBreak = xlNone (or xlPageBreakNone), or
[header].Cells.PageBreak = xlNone,

it did not work throwing the infamous error 1004. Well then, it seems
that your statement works only and exclusively for Cells and you cannot
use it with any other range of even the simplest shape. If I am wrong
in this respect, please, correct me. I'll be very thankful.

Regards
Darlove (PL)

 

[Jim Cone]

Darlove,
Give this format a try.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Sub GetRidOfThem()
Dim rngAll As Excel.Range
Dim rngRow As Excel.Range
Set rngAll = Range("A1:B50").Rows
For Each rngRow In rngAll
If rngRow.EntireRow.PageBreak = xlManual Then
rngRow.EntireRow.PageBreak = xlNone
End If
Next
Set rngRow = Nothing
Set rngAll = Nothing
End Sub
'----------



"darlove" <[email protected]>
wrote in message
Thanx Jim.
It works but to my surprise only in this form, that is with Cells. When
I wanted to do the trick with a continuous range, for example

[header].EntireRow.Cells.PageBreak = xlNone (or xlPageBreakNone), or
[header].Cells.PageBreak = xlNone,

it did not work throwing the infamous error 1004. Well then, it seems
that your statement works only and exclusively for Cells and you cannot
use it with any other range of even the simplest shape. If I am wrong
in this respect, please, correct me. I'll be very thankful.

Regards
Darlove (PL)

 

[darlove]

Hi again.

Well... the news is not as good as you'd probably like it to be. I
slightly made your sub over to read like this:

Sub GetRidOfThem()
Dim rngAll As Excel.Range
Dim rngRow As Excel.Range

Set rngAll = Range("header").CurrentRegion.Rows
For Each rngRow In rngAll
If rngRow.EntireRow.PageBreak <> xlNone Then
rngRow.EntireRow.PageBreak = xlNone
End If
Next
End Sub

and though there is no error this time - which is maybe even more
intriguing - it does not remove any page breaks at all. If this sub
worked as it's supposed to, this would be the end to my questions. Can
you see why it does not do the job it should? (Your version works fine
but removes automatic page breaks only and I want to dump them all from
the Range("header").CurrentRegion area.)

By the way, why do you set the object variables at the end of your sub
to Nothing? Isn't it that when the sub ends, then the variables go out
of scope and the objects that the variables point to are destroyed
automatically? Actually, in this context the objects cannot be
destroyed whatsoever because they HAVE TO exist for the sake of Excel's
very nature... Excel cannot destroy any range that exists on a sheet.
Am I right or not? Any reply will, of course, be much appreciated.

Regards
Darlove (PL)

 

[Jim Cone]

It is you that has the "problem" not me.
Automatic page breaks are just that, you can override them
by using manual page breaks but you cannot remove them.

Several years ago Ken Getz described a memory leak problem
that a client of his had with a program Getz had written.
It turned out, as I recall, that the program had multiple object
references to the same object and not all of them were being
destroyed. I have been setting all object references to Nothing
since then.
If you ever write code that automates Excel you will find out the
benefit of destroying object references.

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



Hi again.
Well... the news is not as good as you'd probably like it to be. I
slightly made your sub over to read like this:

Sub GetRidOfThem()
Dim rngAll As Excel.Range
Dim rngRow As Excel.Range

Set rngAll = Range("header").CurrentRegion.Rows
For Each rngRow In rngAll
If rngRow.EntireRow.PageBreak <> xlNone Then
rngRow.EntireRow.PageBreak = xlNone
End If
Next
End Sub

and though there is no error this time - which is maybe even more
intriguing - it does not remove any page breaks at all. If this sub
worked as it's supposed to, this would be the end to my questions. Can
you see why it does not do the job it should? (Your version works fine
but removes automatic page breaks only and I want to dump them all from
the Range("header").CurrentRegion area.)

By the way, why do you set the object variables at the end of your sub
to Nothing? Isn't it that when the sub ends, then the variables go out
of scope and the objects that the variables point to are destroyed
automatically? Actually, in this context the objects cannot be
destroyed whatsoever because they HAVE TO exist for the sake of Excel's
very nature... Excel cannot destroy any range that exists on a sheet.
Am I right or not? Any reply will, of course, be much appreciated.

Regards
Darlove (PL)

 

[darlove]

Yep, I know it is me. Sorry about that.

I'll try to turn some of the breaks into manual ones, and then apply
your sub. Hope it'll work. As to the second part of your post... Well,
after all you may be right. I have heard here and there that one should
fall into the habit of setting object variables to Nothing at the end
of a sub. On the other hand, in the official documentation it is said
that once the variables go out of scope they disappear and the referred
objects are being destroyed. Perhaps this mechanism does not work that
well after all. Anyway, thank you for taking interest in MY PROBLEM.
You helped me a lot, indeed.

Regards
Darlove (PL)