Unable to set PageBReak property of Range Class?

olimits7

Board Regular
Joined
Oct 29, 2004
Messages
229
Office Version
  1. 365
Platform
  1. Windows
I keep on getting this error when I try to run some VBA code that a former employee wrote, I can't seem to find what the issue is but this is where it stops running.

It stops at ActiveCell.PageBreak = xlPageBreakManual, can someone please point me in the right direction to try and figuring this issue out?

Code:
Columns("T:Z").EntireColumn.Hidden = True
        
    Sheets("tm").Select
        'add pagebreak
    Dim myStop As Long
    myStop = Range("R1").CurrentRegion.Rows.count
    
    For myRow = 1 To myStop
        If Cells(myRow, 1) = "" Then
            Cells(myRow + 1, 1).Select
            ActiveCell.PageBreak = xlPageBreakManual
        End If
    Next myRow

Thank you!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Change this line

Code:
ActiveCell.PageBreak = xlPageBreakManual

For this

Code:
ActiveSheet.HPageBreaks.Add Before:=ActiveCell

Try and tell me.
 
Upvote 0
I got this error now "You can't add more than 1026 horizontal page breaks to a worksheet."
 
Last edited:
Upvote 0
You are adding too many page break.

You are adding a page break for each empty cell in column A.

Try this

Code:
Sub Macro1()
  Dim myRow, myStop
  Columns("T:Z").EntireColumn.Hidden = True
  Sheets("tm").Select
  'add pagebreak
[COLOR=#0000ff]  myStop = Range("A" & Rows.Count).End(xlUp).Row[/COLOR]
  For myRow = 1 To myStop
    If Cells(myRow, 1) = "" Then
      Cells(myRow + 1, 1).Select
      ActiveSheet.HPageBreaks.Add Before:=ActiveCell
    End If
  Next myRow
End Sub
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top