Hi there
I'm using Microsoft excel 2007, in window Vista. I got this problem to
paste rows when it is more than 65,000 row.

Eg. Worksheet A already have 40,000 rows. I want to copy data from worksheet
B with total 29,000 rows tp wprksheet A. The no of column i have is 15
columns in A worksheet.

It dont allow me to paste the data in worksheet B to worksheet A, 40,000
row+29.000 rows=69,000 rows. Error message appeared.

"The info cannot be pasted because the copy area and the paste area are not
the same size and shape. Try one of the following:
1) Click a single cell, then paste
2)Select a rectangle that the same size and shape, Then paste."

I also checked that when i pasted 20,000 rows, I could paste it in Worksheet
A.
Is there a limitation in no. of rows ?

My file currently is saved under Excel 97-2003 workbook. xls. type.
I tried save the file in "Excel Workbook(*.xlsx), it doesn't help either.

Kindly asist me for above problem.

Office email:


Thank you
Channe Lim
Singapore

Hi

After saving as an xlsx file, you need to close it, then re-open.
Then you should have the ability to have up to 1 million rows.

--
Regards
Roger Govier



"Channe" <(E-Mail Removed)> wrote in message
news:7110BC80-D71D-4F29-8825-(E-Mail Removed)...
> Hi there
> I'm using Microsoft excel 2007, in window Vista. I got this problem to
> paste rows when it is more than 65,000 row.
>
> Eg. Worksheet A already have 40,000 rows. I want to copy data from
> worksheet
> B with total 29,000 rows tp wprksheet A. The no of column i have is 15
> columns in A worksheet.
>
> It dont allow me to paste the data in worksheet B to worksheet A, 40,000
> row+29.000 rows=69,000 rows. Error message appeared.
>
> "The info cannot be pasted because the copy area and the paste area are
> not
> the same size and shape. Try one of the following:
> 1) Click a single cell, then paste
> 2)Select a rectangle that the same size and shape, Then paste."
>
> I also checked that when i pasted 20,000 rows, I could paste it in
> Worksheet
> A.
> Is there a limitation in no. of rows ?
>
> My file currently is saved under Excel 97-2003 workbook. xls. type.
> I tried save the file in "Excel Workbook(*.xlsx), it doesn't help either.
>
> Kindly asist me for above problem.
>
> Office email:
>
>
> Thank you
> Channe Lim
> Singapore
>

You do not say so, but it seems you are using XL2007.
The row limit for XL2007 is 1,048,576
If I copy some data from an XL2003 file to an new XL2007 file and use
CTRL+END it shows I have 1,048,576 rows.
Not sure why you are having trouble.
Does the title bar (very top of Excel window) show "Compatibility mode" ? If
so then XL2007 is behaving like XL2003. Try using a new file.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Channe" <(E-Mail Removed)> wrote in message
news:7110BC80-D71D-4F29-8825-(E-Mail Removed)...
> Hi there
> I'm using Microsoft excel 2007, in window Vista. I got this problem to
> paste rows when it is more than 65,000 row.
>
> Eg. Worksheet A already have 40,000 rows. I want to copy data from
> worksheet
> B with total 29,000 rows tp wprksheet A. The no of column i have is 15
> columns in A worksheet.
>
> It dont allow me to paste the data in worksheet B to worksheet A, 40,000
> row+29.000 rows=69,000 rows. Error message appeared.
>
> "The info cannot be pasted because the copy area and the paste area are
> not
> the same size and shape. Try one of the following:
> 1) Click a single cell, then paste
> 2)Select a rectangle that the same size and shape, Then paste."
>
> I also checked that when i pasted 20,000 rows, I could paste it in
> Worksheet
> A.
> Is there a limitation in no. of rows ?
>
> My file currently is saved under Excel 97-2003 workbook. xls. type.
> I tried save the file in "Excel Workbook(*.xlsx), it doesn't help either.
>
> Kindly asist me for above problem.
>
> Office email:
>
>
> Thank you
> Channe Lim
> Singapore
>

Has the increase in the row limit in xl2007 also increased the
operability of the built-in TRANSPOSE and INDEX functions?

Alan Beban
Bernard Liengme wrote:
> You do not say so, but it seems you are using XL2007.
> The row limit for XL2007 is 1,048,576
> If I copy some data from an XL2003 file to an new XL2007 file and use
> CTRL+END it shows I have 1,048,576 rows.
> Not sure why you are having trouble.
> Does the title bar (very top of Excel window) show "Compatibility mode" ? If
> so then XL2007 is behaving like XL2003. Try using a new file.
> best wishes

I would expect TRANSPOSE and INDEX would take full advantage of the large
worksheet.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Alan Beban" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Has the increase in the row limit in xl2007 also increased the operability
> of the built-in TRANSPOSE and INDEX functions?
>
> Alan Beban
> Bernard Liengme wrote:
>> You do not say so, but it seems you are using XL2007.
>> The row limit for XL2007 is 1,048,576
>> If I copy some data from an XL2003 file to an new XL2007 file and use
>> CTRL+END it shows I have 1,048,576 rows.
>> Not sure why you are having trouble.
>> Does the title bar (very top of Excel window) show "Compatibility mode" ?
>> If so then XL2007 is behaving like XL2003. Try using a new file.
>> best wishes

Could someone run the following code in Excel2007 with n=65535 and then
n=65537 and report the results?

Sub abtest1
Dim arr1(), arr2(), iVar, n
n = 65535
'n = 65537
ReDim arr1(1 To n, 1 To 1)
arr1(4, 1) = "ok"
On Error Resume Next
arr2 = Application.Transpose(arr1)
If Err <> 0 Then
MsgBox "Transpose doesn't work"
Err = 0
End If
iVar = Application.Index(arr1, 4, 1)
If Err <> 0 Then
MsgBox "Index doesn't work"
Err = 0
End If
Debug.Print arr2(4), iVar
End Sub

Thanks,
Alan Beban

Bernard Liengme wrote:
> I would expect TRANSPOSE and INDEX would take full advantage of the large
> worksheet.
> best wishes

Is there a setting somewhere in Excel 2007 that enforces the 65K row
limit on worksheets in workbooks saved in an earlier version? I could
see a need for that kind of thing in some situations.

Mark Lincoln

On Sep 11, 6:50 am, Channe <Cha...@discussions.microsoft.com> wrote:
> Hi there
> I'm using Microsoft excel 2007, in window Vista. I got this problem to
> paste rows when it is more than 65,000 row.
>
> Eg. Worksheet A already have 40,000 rows. I want to copy data from worksheet
> B with total 29,000 rows tp wprksheet A. The no of column i have is 15
> columns in A worksheet.
>
> It dont allow me to paste the data in worksheet B to worksheet A, 40,000
> row+29.000 rows=69,000 rows. Error message appeared.
>
> "The info cannot be pasted because the copy area and the paste area are not
> the same size and shape. Try one of the following:
> 1) Click a single cell, then paste
> 2)Select a rectangle that the same size and shape, Then paste."
>
> I also checked that when i pasted 20,000 rows, I could paste it in Worksheet
> A.
> Is there a limitation in no. of rows ?
>
> My file currently is saved under Excel 97-2003 workbook. xls. type.
> I tried save the file in "Excel Workbook(*.xlsx), it doesn't help either.
>
> Kindly asist me for above problem.
>
> Office email:
>
> Thank you
> Channe Lim
> Singapore

Hi Alan

Code runs and produces the debug result
ok ok

--
Regards
Roger Govier



"Alan Beban" <(E-Mail Removed)> wrote in message
news:ex%(E-Mail Removed)...
> Could someone run the following code in Excel2007 with n=65535 and then
> n=65537 and report the results?
>
> Sub abtest1
> Dim arr1(), arr2(), iVar, n
> n = 65535
> 'n = 65537
> ReDim arr1(1 To n, 1 To 1)
> arr1(4, 1) = "ok"
> On Error Resume Next
> arr2 = Application.Transpose(arr1)
> If Err <> 0 Then
> MsgBox "Transpose doesn't work"
> Err = 0
> End If
> iVar = Application.Index(arr1, 4, 1)
> If Err <> 0 Then
> MsgBox "Index doesn't work"
> Err = 0
> End If
> Debug.Print arr2(4), iVar
> End Sub
>
> Thanks,
> Alan Beban
>
> Bernard Liengme wrote:
>> I would expect TRANSPOSE and INDEX would take full advantage of the large
>> worksheet.
>> best wishes

Thank you, Roger. I guess the next question is "How big can n be?"

Alan Beban

Roger Govier wrote:
> Hi Alan
>
> Code runs and produces the debug result
> ok ok
>

Bernard Liengme wrote:
> I would expect TRANSPOSE and INDEX would take full advantage of the large
> worksheet.
> best wishes
One might expect that without checking it; but Jim Rech's reply in the
programming newsgroup at 2:01pm today suggests otherwise.

Thanks anyway,
Alan Beban