Application Error: ActiveSheet.VPageBreaks(1).DragOff

Seanzies

Board Regular
Joined
Nov 19, 2005
Messages
212
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

Any suggestions why?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Nov 19, 2005
Messages
212
Andrew, I appreciate your response.

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

Seanzies

Board Regular
Joined
Nov 19, 2005
Messages
212

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Nov 19, 2005
Messages
212

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Well it's probably because you haven't got any VPageBreaks the second time round. Try:

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

jyothisvikram

New Member
Joined
Jun 26, 2008
Messages
1
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
 

Forum statistics

Threads
1,137,208
Messages
5,680,196
Members
419,889
Latest member
ballsofspartans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top