Need more rows.. Excel 2007

[l337azza]

Hi All,

I have a problem... I have 2 matrices that i need to have combined in 2 columns next to each other.

I have already d/l a VB macro that enables me to run and combine the data parallel to each other in two columns, however there is too much data and it runs out of rows in Excel 2007. I dont know if there may be an event clause in Excel that automatically takes the corresponding data and can paste it into new columns ?

Would there be any VB gurus out there that could help me/show me how to automatically move the data into either two new columns.

thanking you

 

[GlennUK]

So you are saying that you have in excess of 1048576 rows of output?

 

[l337azza]

Correct, I am doing some data analysis and yes it runs out :/

did you want me to post a sample ?

 

[GlennUK]

If you could describe your data, and post the macro, that would be a start.

 

[l337azza]

I have multiple columns and thousands of rows...below is a small example..
<table border="0" cellpadding="0" cellspacing="0" width="320"><col style="width: 48pt;" width="64" span="5"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">
</td> <td style="width: 48pt;" width="64">de003</td> <td style="width: 48pt;" width="64">de004</td> <td style="width: 48pt;" width="64">de005</td> <td style="width: 48pt;" width="64">de006</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">de003</td> <td class="xl4493" align="right">0.000</td> <td class="xl4493">
</td> <td class="xl4493">
</td> <td class="xl4493">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">de004</td> <td class="xl4493" align="right">0.000</td> <td class="xl4493" align="right">0.000</td> <td class="xl4493">
</td> <td class="xl4493">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">de005</td> <td class="xl4493" align="right">0.000</td> <td class="xl4493" align="right">0.000</td> <td class="xl4493" align="right">0.000</td> <td class="xl4493">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">de006</td> <td class="xl4493" align="right">0.000</td> <td class="xl4493" align="right">0.000</td> <td class="xl4493" align="right">0.000</td> <td class="xl4493" align="right">0.000</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">de007</td> <td class="xl4493" align="right">310.597</td> <td class="xl4493" align="right">310.597</td> <td class="xl4493" align="right">310.597</td> <td class="xl4493" align="right">310.597</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">de008</td> <td class="xl4493" align="right">152.501</td> <td class="xl4493" align="right">152.501</td> <td class="xl4493" align="right">152.501</td> <td class="xl4493" align="right">152.501</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">de009</td> <td class="xl4493" align="right">152.501</td> <td class="xl4493" align="right">152.501</td> <td class="xl4493" align="right">152.501</td> <td class="xl4493" align="right">152.501
</td> </tr> </tbody></table>I have two works sheets that are identical in size just different data...the macro that i have enables me to copy the range and paste it into a column.. please see below...i just need to know how to make the data move into a new column when the length of that is reached...

Sub test()
Dim v As Variant
Dim nCol As Long
Dim nRow As Long
Dim rOut As Range
Dim iCol As Long

On Error Resume Next
v = Application.InputBox("Select range to copy", Type:=8).Value
If IsEmpty(v) Then Exit Sub
nRow = UBound(v, 1)
nCol = UBound(v, 2)

Set rOut = Application.InputBox("Select destination", Type:=8).Resize(nRow, 1)
If rOut Is Nothing Then Exit Sub

For iCol = 1 To nCol
rOut.Value = WorksheetFunction.Index(v, 0, iCol)
Set rOut = rOut.Offset(nRow)
Next iCol
End Sub

 

[GlennUK]

Well, you could test to see if the next block goes over the limit, and just start the next column with a complete block ... you'd have blanks at the end of the column ... would that be OK? Like:

For iCol = 1 To nCol
rOut.Value = WorksheetFunction.Index(v, 0, iCol)
If rOut.Row + 2*nRow > Cells.Rows.Count Then
   Set rOut = rOut.Offset(-rOut.Row+1,1)
Else
   Set rOut = rOut.Offset(nRow)
End If
Next iCol