Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
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 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
Hi Alan
Code runs and produces the debug result ok ok -- Regards Roger Govier "Alan Beban" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
Hi Alan
n 65536 works n 65537 fails on Transpose -- Regards Roger Govier "Alan Beban" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
Thanks. And on INDEX? I would expect it returns two message boxes
indicating it fails on both. Alan Beban Roger Govier wrote: Hi Alan n 65536 works n 65537 fails on Transpose |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
Hi Alan
Yes, it fails on Index as well. I had made a small For ... Next loop, and made it exit sub when it hit the first failure message. -- Regards Roger Govier "Alan Beban" wrote in message ... Thanks. And on INDEX? I would expect it returns two message boxes indicating it fails on both. Alan Beban Roger Govier wrote: Hi Alan n 65536 works n 65537 fails on Transpose |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can exceed 65,000rows in Excel?
Thank you.
Yes; all you had to do was paste the code; I had fiddled with it in order to get both message boxes. Thanks again, Alan Roger Govier wrote: Hi Alan Yes, it fails on Index as well. I had made a small For ... Next loop, and made it exit sub when it hit the first failure message. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exceed Row Limit | Excel Worksheet Functions | |||
Exceed Row Limit | Excel Worksheet Functions | |||
Matching a value, which does not exceed Min or Max. | Excel Worksheet Functions | |||
Excel columns do they exceed IV? | Excel Worksheet Functions | |||
Exceed Scale | Charts and Charting in Excel |