Can't create enough rows

[RodneyC]

I have data from two Excel files that I need to combine into one worksheet, one tab. Both files have roughly 65000 lines. When I try to copy/paste from one to the other, I get an error message saying "you can't paste this here because the Copy area and the paste area aren't the same size. Select just one cell in the paste area that's the same size and try pasting again." All columns and all rows are exactly the same size. The issue seems to be actually related to the total number of rows.

The document I am pasting in to originally had 65126 rows. I can paste the first 410 or so rows from the second document into the destination file with no problem. When I try to add more rows I get this error message. "Microsoft Excel can't insert new cells because it would push non-empty cells off the end of the worksheet. These empty cells might appear to be empty but have blank values, some formatting, or a formula. Delete enough rows or columns to make enough room for what you want to insert and try again."

I don't want to delete any rows, I need to add more. How can I add more rows?

Thanks in advance.

 

[alansidman]

Use Power Query to append them to each other. Load each range to the PQ editor and on the Transform Tab, append each to the other.

 

[Joe4]

Are you dealing with the old "xls" file format? Those only allowed 65536 rows (which "adds up", if you check your numbers).
If you convert the files to the newer "xlsx" file format, those allow over 1 million rows, so you won't have any isssues.
You should simply be able to convert the files simply by opening them in Excel, and doing a SaveAs, and choose the "xlsx" option.

 

[RodneyC]

Are you dealing with the old "xls" file format? Those only allowed 65536 rows (which "adds up", if you check your numbers).
If you convert the files to the newer "xlsx" file format, those allow over 1 million rows, so you won't have any isssues.
You should simply be able to convert the files simply by opening them in Excel, and doing a SaveAs, and choose the "xlsx" option.

The files were exported from a 3rd party and yes, they were the old .xls format. Thanks, this solved the issue.

 

[Joe4]

You are welcome.

 

[SpillerBD]

I used to face XLS files that needed to be upgraded all the time.
Using the SAVE AS command creates an additional file. Why have 2 files?
Under the FILE menu, the CONVERT button will be available for non-XLSX/XLSM files. Use that instead to avoid the duplicate file creation, unless you really want to keep the original XLS file.
If your environment will have you using the Upgrade Button frequently, you can add it to the QAT. It is listed as Convert to Open Office XML. Only caution would be if the XLS file contains macros, then use save as, and delete the XLS after.

 

[Joe4]

I used to face XLS files that needed to be upgraded all the time.
Using the SAVE AS command creates an additional file. Why have 2 files?
Under the FILE menu, the CONVERT button will be available for non-XLSX/XLSM files. Use that instead to avoid the duplicate file creation, unless you really want to keep the original XLS file.
If your environment will have you using the Upgrade Button frequently, you can add it to the QAT. It is listed as Convert to Open Office XML. Only caution would be if the XLS file contains macros, then use save as, and delete the XLS after.

Or better yet, see if that 3rd party app has an option to export to the newer Excel ("xlsx") format.
Sometimes, processes were created years ago, and never updated, though the program itself may have been updated and now offers an "xlsx" option.

 

[Automatrix]

XLS horror story: Excel: Why using Microsoft's tool caused Covid-19 results to be lost

 

[Joe4]

XLS horror story: Excel: Why using Microsoft's tool caused Covid-19 results to be lost

That is some great quality checking right there!
The tools are only as good as the people who use them.

It is amazing how antiquated some of the computing systems that large corporations and governments still use are.

 

[Automatrix]

Yep, the tools are only as good as the people who use them!
It is amazing how antiquated some of the computing systems that large corporations and governments still use are.

Ironically, I've most often run into the xls problem in smaller, newer systems. Customers ask for Excel export options, software developers don't work with Excel themselves and don't know that xlsx even exists.