Splitting records to two rows

T

Tom Nichol

I have a report that has many columns; most of them not very wide, however the report is too wide to fit even legal size sheets in landscape mode, without reducing the font to a "difficult to read" size. I realize I can split the report vertically into two pages, however, I would prefer not to. Does anyone know of a way to split each record into two rows without using an inefficient cut and paste process?
 
J

jeff

Hi,

Try this macro on a test sheet.

Sub Make2_rows()
Dim lastrow As Long
Dim row_index As Long
Dim ColtoSplit As Integer
ColtoSplit = 4
' ColtoSplit is the column number to start new 4="D"
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = lastrow To 1 Step -1
Range(Cells(row_index, ColtoSplit), Cells(row_index,
256)).Cut
Range(Cells(row_index + 1, 1), Cells(row_index + 1,
1)).Select
Selection.Insert xlShiftDown
Next
Application.ScreenUpdating = True
End Sub


This splits each row at column D (#4).

jeff
-----Original Message-----
I have a report that has many columns; most of them not
Click to expand...
very wide, however the report is too wide to fit even
legal size sheets in landscape mode, without reducing the
font to a "difficult to read" size. I realize I can
split the report vertically into two pages, however, I
would prefer not to. Does anyone know of a way to split
each record into two rows without using an inefficient
cut and paste process?
 
B

Bill Kuunders

Use the sort option

First insert a column with uneven numbers 1,3,5,(use fillhandle for the
rest)
insert another column where you want to split stuff with even numbers
2,4,(use fillhandle for the rest)
move the right hand section under the left and do a sort of the total area
on column A

Regards
Bill K

Tom Nichol said:
I have a report that has many columns; most of them not very wide, however
Click to expand...
the report is too wide to fit even legal size sheets in landscape mode,
without reducing the font to a "difficult to read" size. I realize I can
split the report vertically into two pages, however, I would prefer not to.
Does anyone know of a way to split each record into two rows without using
an inefficient cut and paste process?
 
T

Tom Nichol

Jeff,

Thanks for the help, however, when I ran the macro on a test sheet, I got an error message reading "Compile Error: Syntax Error". Line 4 of the macro was highlighted at the time it stopped running. I'm afraid I'm not conversant enough with macros to identify the specific problem.

Tom
 
D

Dave Peterson

I'm not sure which is line 4 for you, but maybe it was a line wrap problem.
Jeff's code worked ok for me when I fixed that.

Here's a linewrap problem free version <bg>.

Option Explicit
Sub Make2_rows()
Dim lastrow As Long
Dim row_index As Long
Dim ColtoSplit As Integer
ColtoSplit = 4
' ColtoSplit is the column number to start new 4="D"
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = lastrow To 1 Step -1
Range(Cells(row_index, ColtoSplit), Cells(row_index, 256)).Cut
Range(Cells(row_index + 1, 1), Cells(row_index + 1, 1)).Select
Selection.Insert xlShiftDown
Next
Application.ScreenUpdating = True
End Sub
 
T

Tom Nichol

I guess it must have been a line wrap problem!
It works fine now.

Many thanks, Jeff & Dave!
 
Top