# formatting to fit on one page

#### fedds2

##### New Member
If you have 4 rows of data and say 60 columns, how can you write a macro to find the edge of page and move the data nominally on pages 2 and 3 to appear on page 1. In other words rows 1 to 4 have page 1 data, rows 9-13 have page 2 data and rows 17-21 have page 3 data.
Clear as mud ?

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### scifibum

##### Well-known Member
Just so you know, this code runs really slowly. I think the "vpagebreaks" property is really inefficient, although I don't know why.

Please note the workaround with selecting the last cell on the sheet, the vpagebreaks property often fails without it. see the URL referenced for details.

Rich (BB code):
``````Sub MoveData()
Dim breaks As Integer, i As Integer
Dim col1 As Integer, col2 As Integer
Dim rngCut As Range
Dim pasterow As Integer

breaks = ActiveSheet.VPageBreaks.Count

pasterow = 9
Range("IV65536").Select ' this is a workaround for buggy behavior, see http://support.microsoft.com/default.aspx?scid=kb;en-us;210663
For i = 1 To breaks
If i < breaks Then
col1 = ActiveSheet.VPageBreaks(i).Location.Column
col2 = ActiveSheet.VPageBreaks(i + 1).Location.Column - 1
Else
col1 = ActiveSheet.VPageBreaks(i).Location.Column
col2 = Range("IV1").End(xlToLeft).Column
End If
Set rngCut = Range(Cells(1, col1), Cells(4, col2))
rngCut.Cut Range("A" & pasterow)
pasterow = pasterow + 8
Next i

End Sub``````

#### fedds2

##### New Member
Thanks Keith I'll give it a whirl.

Replies
1
Views
100
Replies
18
Views
245
Replies
1
Views
182
Replies
2
Views
63
Replies
0
Views
81

1,118,290
Messages
5,571,327
Members
412,382
Latest member
Langtn02