Application Error: ActiveSheet.VPageBreaks(1).DragOff

[Seanzies]

I am getting an application-definer or object-defined error on the following line:

LastRowColB = Sheets("Printed").Range("B65536").End(xlUp).Row + 2

        X = "$AE$" & LastRowColB

        Sheets("Printed").Select
        ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 'Sets the Page Break Preview
        ActiveSheet.PageSetup.PrintArea = "$A$1" & ":" & X

I get the error on this line

ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 'Sets the Page Break Preview

Any suggestions why?

 

[Andrew Poulsom]

From MSDN:

DragOff Method [Excel 2003 VBA Language Reference]
Drags a page break out of the print area.

expression.DragOff(Direction, RegionIndex)

expression Required. An expression that returns one of the objects in the Applies To list.

Direction Required XlDirection . The direction in which the page break is dragged.

XlDirection can be one of these XlDirection constants.
xlDown
xlToRight
xlToLeft
xlUp

RegionIndex Required Long. The print-area region index for the page break (the region where the mouse pointer is located when the mouse button is pressed if the user drags the page break). If the print area is contiguous, there's only one print region. If the print area is discontiguous, there's more than one print region.

Remarks
This method exists primarily for the macro recorder. You can use the Delete method to delete a page break in Visual Basic.

Note the Remarks.

 

[Seanzies]

Andrew, I appreciate your response.

How would I go about deleting the vertical page break using VBA?

 

[Andrew Poulsom]

Like this?

ActiveSheet.VPageBreaks(1).Delete

 

[Seanzies]

I tried to ActiveSheet.VPageBreaks(1).Delete
but I get the same error message when I try to run this line.

Can you try to better explain what i need to do to my code so that I will not get this error message.

 

[Andrew Poulsom]

What error do you get? I tried it before I posted and it worked for me (Excel 2000). I did get a subscript out of range error if there were no VPageBreaks.

 

[Seanzies]

Like my original post

I am getting an application-definer or object-defined error on the following line:


Code:
LastRowColB = Sheets("Printed").Range("B65536").End(xlUp).Row + 2

X = "$AE$" & LastRowColB

Sheets("Printed").Select
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 'Sets the Page Break Preview
ActiveSheet.PageSetup.PrintArea = "$A$1" & ":" & X


I get the error on this line
Code:
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1 'Sets the Page Break Preview


If you run the code for the first time it works fine. But if you run the code a second time then I get the application-definer or object-defined error

 

[Andrew Poulsom]

Well it's probably because you haven't got any VPageBreaks the second time round. Try:

With ActiveSheet
   If .VPageBreaks.Count > 0 Then
      .VPageBreaks(1).Delete
   End If
End With

 

[jyothisvikram]

Hi..

Even i am getting the same error but it is not solved even with the above solution. there is a vertical page break and if i try to drag/delete it.. VBA throws the same error.. not even working 1st time.. "Application defined or object defined error"

kindly help

Regards
Jyo