Solved: PageBreak Macro Slow

[empty]

Howdy,

This is my first post...Thanks for help.

The following code works but runs very slow:

[vba]Sub AutoPageBreak()
Dim LastRow As Long
Dim Count As Long
Application.ScreenUpdating = False

LastRow = Worksheets("Audit").Range("D65536").End(xlUp).Row
LastRow = LastRow + 1

For Count = 4 To LastRow

If Cells(Count, "B") <> Cells(Count - 1, "B") Then

Worksheets("Audit").Rows(Count).PageBreak = xlPageBreakManual

End If

Next Count

Application.ScreenUpdating = True

End Sub [/vba]

It sets pagebreaks between names in column B when they are different. There are thousands (3-6k) of rows with data and at least 300 pagebreaks.

My questions are: Is it slow due to clunky coding or does setting the pagebreaks bog it down? Is there a more elegant solution?

I combat code to get things working, but I would like to improve my understanding of VBA.

Thanks again...

[XLGibbs]

See if this is any faster, not tremendously different actions, but a different approach...

[VBA]
Sub AutoPageBreak()
Dim LastRow As Long
Dim Count As Long
Dim aRng As Range, c As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
With Worksheets("Sheet1")
LastRow = .Range(.rows.count).End(xlUp).Offset(1).row
Set aRng = .Range("B5:B" & LastRow)
For Each c In aRng
If c <> c.Offset(-1) Then c.PageBreak = xlManual
Next c
Application.EnableEvents = True

Application.ScreenUpdating = True
End With
End Sub
[/VBA]

[matthewspatrick]

Part of why the sub was running so slowly was that you looped through a whole range.

As a test, I populated A2:A20001 with randdom numbers from 1 to 100, then sorted them. In A1, I put a dummy header. I then executed this code to insert manual page breaks at each change in number:


[VBA]
Sub DoPgBreak()

Dim NumRows As Long
Dim rng As Range

[b1] = "x"

NumRows = [a65536].End(xlUp).Row
[b2] = 0
Range([b3], "b" & NumRows).Formula = "=IF(A2<>A3,1,0)"

[b1].AutoFilter Field:=2, Criteria1:=1, Operator:=xlAnd

For Each rng In Range("a2:a" & NumRows).SpecialCells(xlCellTypeVisible).Cells
rng.PageBreak = xlManual
Next

With [b1]
.AutoFilter
.EntireColumn.Delete
End With

End Sub
[/VBA]

It took about 7 seconds to run, because it did not need to evaluate every cell.

Turning off screen updates would have helped

Patrick

[empty]

Thanks for the quick reply,

I get a "susbscript out of range error" (Run time error '9') at:

With Worksheets("Sheet1")

If I change the name to "Audit", I get an "Application defined or Object defined error" (run time error '1004') at:

LastRow = .Range(.Rows.Count).End(xlUp).Offset(1).Row

Thoughts?

[empty]

Turning off screen updates would have helped

Patrick

I don't know vba for diddly but I got that...


I'm going to have to work through the code to understand what's going on. My macro takes about 45 seconds to "fly" through the 3000 rows in the range (screen updates off).

Thanks guys...this VBA site is most excellent.

[XLGibbs]

Thanks for the quick reply,

I get a "susbscript out of range error" (Run time error '9') at:

With Worksheets("Sheet1")

If I change the name to "Audit", I get an "Application defined or Object defined error" (run time error '1004') at:

LastRow = .Range(.Rows.Count).End(xlUp).Offset(1).Row

Thoughts?

Well you had to name the sheet "Audit"....my bad for not correcting the sample.

the other error was also my bad, s/b

lastrow = Cells(1,.rows.count).End(xlup).Offset(1).Row

[empty]

Patrick,

I understand everything in the code you posted except the most critical part:

For Each rng In Range("a2:a" & NumRows).SpecialCells(xlCellTypeVisible).Cells

I tried to serch the vbe help files for SpecialCells(xlCellTypeVisible).Cells and was unable to find it. Could you point me in the right direction?

Thanks,
Mike

[empty]

Well you had to name the sheet "Audit"....my bad for not correcting the sample.

the other error was also my bad, s/b

lastrow = Cells(1,.rows.count).End(xlup).Offset(1).Row

Thanks....I'll try it out.

Mike

[matthewspatrick]

Patrick,

I understand everything in the code you posted except the most critical part:

For Each rng In Range("a2:a" & NumRows).SpecialCells(xlCellTypeVisible).Cells

I tried to serch the vbe help files for SpecialCells(xlCellTypeVisible).Cells and was unable to find it. Could you point me in the right direction?

Mike,

The SpecialCells method (try searching on SpecialCells in the Excel VBA help) returns a range collection of, well, "special" cells

Depending on the argument you supply, it may be the cells within a range that are visible, that are blank, that are not blank, the last cell, etc.

So, that line in the code says, "grab every cell in the range A2:A<whatever> that is visible right now, and loop through just those cells". (Most of the cells are invisible because they did not meet the filter criteria.) The For...Each...Next loop allows you to loop through the members of a collection, and is very handy when you need to enumerate the members of a collection but you do not necessarily know the sizes, indices, etc.

Patrick

[empty]

Thanks guys...Both of you were very helpful.