Looks like you cleaned up the big ones. Good luck.

"Brad" wrote:

The following is giving better results - still needs some work... Is there a
better way?? There blank rows are a given and it is inserting the proper
text.

Sub test3()
Dim TotalHeight As Double
Dim MaxHeight As Double
Dim PRow As Integer
Dim CRow As Integer
Dim RCnt As Long

Dim rngSHT1 As Range
Dim rngColA As Range

MaxHeight = 700

Set rngColA = shtGPA9D.Range("A55:A176")
TotalHeight = 0
PRow = 0
CRow = 0

For RCnt = 1 To 124
If rngColA(RCnt) = "" Then
PRow = CRow + 54
CRow = RCnt + 54
End If

TotalHeight = TotalHeight + rngColA(RCnt).RowHeight

If TotalHeight MaxHeight Then
shtGPA9D.Rows(PRow & ":" & PRow + 3).Insert
shtGPA9D.Range("J" & PRow & ":J" & PRow + 3).Value =
shtGPA9D.Range("RightVF1:RightVF4").Value
shtGPA9D.Range("J" & PRow & ":J" & PRow + 3).HorizontalAlignment
= xlRight
shtGPA9D.HPageBreaks.Add befo=Cells(PRow + 4, "a")

shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4)
TotalHeight = 0
End If
Next RCnt
End Sub



"JLGWhiz" wrote:

You also need to define what " " means. I assumed it was supposed to be ""
for null string. Then you need to look at your If statement. It will not
work in the present configuration. I changed it to use
SpecialCells(xlCellTypeBlanks) < 1 and got past the error message only to
find that your calculation for the Total height will kick in and try to use
the Prow and Crow variables which were not defined because no blank rows were
found. In other words, it needs some work.

"Brad" wrote:

What the macro is trying to do is to look at the text in column A and insert
a page break in-between paragraphs (after the text exceeds a certain length)
.....

Currently, the logic is bombing at

Set Cellcheck = shtGPA9D("A55:A176").Range

What am I doing wrong??

Sub test3()
Dim TotalHeight As Double
Dim MaxHeight As Double
Dim PRow As Integer
Dim CRow As Integer

Dim rngsht As Range
Dim Cellcheck As Range

MaxHeight = 77

Set rngsht1 = shtGPA9D.Rows("55:176")
Set Cellcheck = shtGPA9D("A55:A176").Range
TotalHeight = 0
PRow = 0
CRow = 0

For Each r In rngsht1
If Cellcheck(r) = " " Then
PRow = CRow + 54
CRow = r + 54
End If

TotalHeight = TotalHeight + r.RowHeight

If TotalHeight MaxHeight Then
shtGPA9D.Rows(PRow & ":" & PRow + 3).Insert
shtGPA9D.Range("J" & PRow & ":J" & PRow + 57).Value =
shtGPA9D.Range("RightVF1:RightVF4").Value
shtGPA9D.Range("J" & PRow + 54 & ":J" & PRow +
57).HorizontalAlignment = xlRight
shtGPA9D.HPageBreaks.Add befo=Cells(PRow + 4, "a")

shtGPA9D.Range("title1").Copy shtGPA9D.Range("a" & PRow + 4)
TotalHeight = 0
End If
Next r
End Sub