Thankyou again Tom
Your second post made me stop and think out side the
square, and that will be the way I go for now.
Thankyou for the code I'll have a look and a play with it
later on, when time permits, it looks intriguing at first
glance.
And thankyou for your time and effort, very much
appreciated.

Season greetings to you and your family.

Bob C

-----Original Message-----
If you want to pursue you first approach, here is some
starter code from I
previous post I made:


Here is a method to get an array of horizontal
pagebreaks and vertical
pagebreaks. The horizontal pagebreaks are a list of
rows that have the
pagebreak and vertical a list of column numbers:

Sub Tester1()
Dim horzpbArray()
Dim verpbArray()
ThisWorkbook.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")"
ThisWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")"
i = 1
While Not IsError(Evaluate("Index(hzPB," & i & ")"))
ReDim Preserve horzpbArray(1 To i)
horzpbArray(i) = Evaluate("Index(hzPB," & i & ")")
i = i + 1
Wend
ReDim Preserve horzpbArray(1 To i - 1)
Debug.Print "Horizontal Pagebreaks (rows):"
For J = LBound(horzpbArray, 1) To UBound(horzpbArray, 1)
Debug.Print J, horzpbArray(J)
Next J

i = 1
While Not IsError(Evaluate("Index(vPB," & i & ")"))
ReDim Preserve verpbArray(1 To i)
verpbArray(i) = Evaluate("Index(vPB," & i & ")")
i = i + 1
Wend
ReDim Preserve verpbArray(1 To i - 1)
Debug.Print "Vertical Pagebreaks (columns):"
For J = LBound(verpbArray, 1) To UBound(verpbArray, 1)
Debug.Print J, verpbArray(J)
Next J
End Sub

This uses an Excel 4 macro to get this information.
This is much faster
than the VBA pagebreak which uses the printer driver and
can be very slow.

The is a pagebreak property of the range. It can be
tested to see if a
pagebreak exists

if rows(6).pagebreak = xlNone then
'No pagebreak
Else
' Has pagebreak
if rows(6).pagebreak = xlPageBreakAutomatic then
'Automatic pagebreak
elseif rows(6).pagebreak = xlPageBreakManual then
' Manual pagebreak
End if
End if


Combining the above gives:

Sub Tester1()
Dim horzpbArray()
Dim verpbArray()
Dim brkType As String
ThisWorkbook.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")"
ThisWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")"
i = 1
While Not IsError(Evaluate("Index(hzPB," & i & ")"))
ReDim Preserve horzpbArray(1 To i)
horzpbArray(i) = Evaluate("Index(hzPB," & i & ")")
i = i + 1
Wend
ReDim Preserve horzpbArray(1 To i - 1)
Debug.Print "Horizontal Pagebreaks (rows):"
For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1)
If Rows(horzpbArray(j)).PageBreak = xlNone Then
brkType = "None"
Else
' Has pagebreak
If Rows(horzpbArray(j)).PageBreak =
xlPageBreakAutomatic Then
brkType = "Automatic"
ElseIf Rows(horzpbArray(j)).PageBreak =
xlPageBreakManual Then
brkType = "Manual"
Else
brkType = "Unknown"
End If
End If

Debug.Print j, horzpbArray(j), brkType
Next j

i = 1
While Not IsError(Evaluate("Index(vPB," & i & ")"))
ReDim Preserve verpbArray(1 To i)
verpbArray(i) = Evaluate("Index(vPB," & i & ")")
i = i + 1
Wend
ReDim Preserve verpbArray(1 To i - 1)
Debug.Print "Vertical Pagebreaks (columns):"
For j = LBound(verpbArray, 1) To UBound(verpbArray, 1)
If Columns(verpbArray(j)).PageBreak = xlNone Then
brkType = "None"
Else
' Has pagebreak
If Columns(verpbArray(j)).PageBreak =
xlPageBreakAutomatic Then
brkType = "Automatic"
ElseIf Columns(verpbArray(j)).PageBreak =
xlPageBreakManual Then
brkType = "Manual"
Else
brkType = "Unknown"
End If
End If

Debug.Print j, verpbArray(j), brkType
Next j
End Sub

Sample Output:
Horizontal Pagebreaks (rows):
1 13 Manual
2 24 Manual
3 39 Manual
4 67 Manual
5 87 Manual
6 114 Automatic
Vertical Pagebreaks (columns):
1 2 Manual
2 6 Automatic

This should get you started.

Regards,
Tom Ogilvy


Robert Christie
wrote in message
...
Thank you for reply Tom.
I'm trying to fully automate the formating from a .CSV
file to a spreadsheet. A second sheet allows the user
to
print all classroom sheets or specific classrooms.
Using
autofilter will not give me the extra blank rows under
last name, hence the requirement to move rows down.

TIA
Bob C

-----Original Message-----
Seems it would be easer to just apply an autofilter
(Data=filter=Autofilter) filter on each room, print,
filter on the next
and so forth. (the dropdown arrows for the filter
don't
print).

--
Regards,
Tom Ogilvy

Robert Christie
wrote in message
...
Hi everyone
I have a list of student names, M/F, D.O.B., Test
results
etc. sorted by their assigned classroom number.
Rooms
numbers can vary in sequence and total off i.e.
4,5,6,9,12,16, etc.etc.
The number of students in each class can also vary
but
a
classroom will always fit on the one page.

I can find the starting row of each room by using a
helper column and inserting a blank row and page
break.

What I would like to do is move the starting row of
room
5 down to row 38 (start of page 2 landscape format).
Then move room 6 down to row 74 (start of page 3
landscape format) and so on until and including the
last
room number, to end up with a classroom on each page
with
spare blank rows for teachers use.

The page size ,margins, headers and footers have
been
set
previously.

Season Greetings to all.
Hope you can help.

TIA

Bob C