Excel 2007 Compatibility Issues

[VegasNath]

Excel 2007 Compatibility Issues

We are being pursuaded to move into Excel 2007, I can barely control my excitement.
I am experiencing some compatibility issues running 2003 code in 2007, so I may be here quite often

Code: Select all

Set ActiveSheet.HPageBreaks(1).Location = Range("A71")

I get Run-time error '9' - Subscript out of range on the above line in 2007 which works in 2003.

Ideally, I need to be able to execute the same code in both 2003 & 2007.

Also:

Code: Select all

Rows("227:65536").Delete Shift:=xlUp

How can I adapt this so that it deletes row 227 to the last row, regardless of version?

Thanks in advance.

[mbarron]

For part two:

Code: Select all

Rows("227:" & range("A227").end(xldown).row).delete

[HansV]

Page breaks in VBA have always been a mess in any version of Excel, it's best to avoid them.

About your second question:

Rows("227:" & Rows.Count).Delete Shift:=xlUp

will work in every version.

[mbarron]

If there is a possibility of gaps in the rows, you could use:

Code: Select all

Rows("227:" & Cells.Find(what:="*", SearchDirection:=xlPrevious).Row).Delete

[HansV]

Microsoft claims to have an explanation AND a workaround for the problem in You receive a "Subscript out of range" error message when you use HPageBreaks or VPageBreaks.Location in Excel.

It doesn't work for me in Excel 2007, however.

If you really want to set page breaks in code it's probably best to delete them all, then to add new page breaks using code like this:

ActiveSheet.HPageBreaks.Add Before:=Range("A71")

This does set a horizontal page break, but the HPageBreaks collection remains empty...

[VegasNath]

Thanks for the various responses.

Hans, I'm not sure that I fully understand your last reply..

Set ActiveSheet.HPageBreaks(1).Location = Range("A69")
...works for me in 2003, but not in 2007.

Your suggestion of:
ActiveSheet.HPageBreaks.Add Before:=Range("A71")
... does not work for me in 2003.

[HansV]

According to Add Method [Excel 2003 VBA Language Reference], HPageBreaks.Add should work. But as I mentioned before, trying to handle page breaks in VBA has always been fraught with problems, so I'm not surprised it fails for you.

[VegasNath]

GRRRRRRRR..... I do need to be able to add the page breaks, this is so annoying.

Just a thought, In your suggestion, Is 'Set' required? If I add it, I get a compile error @ "Before".

If your suggestion works in 2007, but not 2003, I'm wondering if I could use the 'rows.count' method to ascertain the excel version with an if statement generating the different page break code. ?

[HansV]

Set would only be required if you used code like this:

Dim hpb As HPageBreak
Set hpb = ActiveSheet.HPageBreaks.Add(Before:=Range("A71"))

but not if you use

ActiveSheet.HPageBreaks.Add Before:=Range("A71")

I can't test in Excel 2003 at the moment.

[rory]

I just tested in 2003 and it works fine for me.

[VegasNath]

I just tested in 2003 and it works fine for me.

It defo does not work for me in 2003. If anyone can think of a reason why (what I may be doing wrong), I would really appreciate it!

[mbarron]

The following sets a horizontal page break in both 2007 and 2003 before 71 and then readjusted the following PBs accordingly. If I change the range to A69, the PB at 71 remains

Code: Select all

Sub pb()
Dim hpb As HPageBreak
Set hpb = ActiveSheet.HPageBreaks.Add(Before:=Range("A71"))
End Sub

[VegasNath]

Thanks Mike, that does work for me. However, it does not remove the pre-defined pagebreaks. How can I remove all page breaks before setting the ones that I require?

[HansV]

Does

ActiveSheet.ResetAllPageBreaks

work in Excel 2003?

[VegasNath]

If I run:

Set hpb = ActiveSheet.HPageBreaks.Add(Before:=Range("A71"))

This sets a continuous blue line page break.

If I then run:

ActiveSheet.ResetAllPageBreaks

This removes the continuous blue line page break.

However, it does not remove the dotted blue line page breaks which seem to be pre-set.

[HansV]

You can't remove the automatic page breaks, only the manual ones.

[VegasNath]

You can't remove the automatic page breaks, only the manual ones.

But, code like this:

Set ActiveSheet.HPageBreaks(1).Location = Range("A71")

.....does remove the automatic ones. (But does not execute in 2007)

Maybe I'm in a no win situation.

[HansV]

When you set a manual page break, the automatic ones aren't deleted but adjusted. It's possible that none remain visible, easpecially if you have set the print area.

[VegasNath]

Bizarre, but this works for me in both 2003 & 2007.

Code: Select all

Sub pb()

Dim hpb As HPageBreak

    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With

    With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(0.393700787401575)
        .RightMargin = Application.InchesToPoints(0.393700787401575)
        .TopMargin = Application.InchesToPoints(0.393700787401575)
        .BottomMargin = Application.InchesToPoints(0.393700787401575)
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 4
    End With

    ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.PageSetup.PrintArea = "$A$1:$H$226"

    If Rows.Count = 65536 Then
        Set ActiveSheet.HPageBreaks(1).Location = Range("A71")
        Set ActiveSheet.HPageBreaks(2).Location = Range("A97")
        Set ActiveSheet.HPageBreaks(3).Location = Range("A163")
    Else
        Set hpb = ActiveSheet.HPageBreaks.Add(Before:=Range("A71"))
        Set hpb = ActiveSheet.HPageBreaks.Add(Before:=Range("A97"))
        Set hpb = ActiveSheet.HPageBreaks.Add(Before:=Range("A163"))
    End If

End Sub

This removes all automatic page breaks and just leaves the ones that I require, in both versions.

Thanks for all responses.

[HansV]

Glad you were able to solve it.

The reason that the automatic page breaks are removed is that there is no room for them - you specify a print range and set enough manual page breaks to avoid the need for automatic ones within the print area.