This was an excellent solution for me thanks Dave P.
RPP
Quote:
Originally Posted by Dave Peterson
Say you're creating 10 columns per new row.
So it kind of looks like this:
$A$1 $B$1 $C$1 $D$1 $E$1 $F$1 $G$1 $H$1 $I$1 $J$1
$K$1 $L$1 $M$1 $N$1 $O$1 $P$1 $Q$1 $R$1 $S$1 $T$1
$U$1 $V$1 $W$1 $X$1 $Y$1 $Z$1 $AA$1 $AB$1 $AC$1 $AD$1
$AE$1 $AF$1 $AG$1 $AH$1 $AI$1 $AJ$1 $AK$1 $AL$1 $AM$1 $AN$1
$AO$1 $AP$1 $AQ$1 $AR$1 $AS$1 $AT$1 $AU$1 $AV$1 $AW$1 $AX$1
$AY$1 $AZ$1 $BA$1 $BB$1 $BC$1 $BD$1 $BE$1 $BF$1 $BG$1 $BH$1
$BI$1 $BJ$1 $BK$1 $BL$1 $BM$1 $BN$1 $BO$1 $BP$1 $BQ$1 $BR$1
$BS$1 $BT$1 $BU$1 $BV$1 $BW$1 $BX$1 $BY$1 $BZ$1 $CA$1 $CB$1
$CC$1 $CD$1 $CE$1 $CF$1
So column K is stacked under column A (and above all those others).
If one of those columns has a very wide value in it, then it could take a little
experimentation to see what looks the best. (Maybe stacking them 5 columns wide
would look better???)
You can try this and see if it works for you:
Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim iRow As Long
Dim oRow As Long
Dim oCol As Long
Dim MaxCols As Long
Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add
MaxCols = 10 '10 columns per row on the new worksheet
With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 1
LastCol = .Range("Cf1").Column
End With
oRow = -1
With newWks
.Cells.NumberFormat = "@" 'text
For iRow = FirstRow To LastRow
oRow = oRow + 2
oCol = 0
For iCol = FirstCol To LastCol
If oCol = MaxCols Then
oCol = 1
oRow = oRow + 1
Else
oCol = oCol + 1
End If
.Cells(oRow, oCol).Value = curWks.Cells(iRow, iCol).Text
Next iCol
Next iRow
.UsedRange.Columns.AutoFit
End With
End Sub
Dave wrote:
i have a worksheet with very long rows (a to cf). For printing i would like
to wrap the rows to fit on a single page, and then seperate each set with a
space. I would also like to wrap the headers too, of course. is this
possible?
--
Dave Peterson
|