Microsoft Office Excel cannot create or use the data range reference because it is too complex Error

[Finian--]

I am trying to copy/paste the filtered (subtotals) on my worksheet of about 17,000 rows. I do this every week. Unfortunately, I am getting the following error now: Microsoft Office Excel cannot create or use the data range reference because it is too complex. Try one or more of the following:

• Use data that can be selected in one contiguous rectangle.
• Use data from the same sheet.

Both of the above apply to my selection. Any help? Thanks.

[Koman]

When you filter data there is a limit to the number of discontinuous selections that Excel can handle. One solution is to sort the data so that like items are grouped adjacent. Then apply the filter and copy. Alternatively, you can select half of the filtered data and copy it, then select the other half and do it again. If this helps, please click the Yes button.

[AnitG]

You mentioned that there's a limit to the number of discontinuous selections that Excel can handle, do you know what the number is? I have 65K rows and want to know the limitation so I know how many times I have to repeat the steps of copying discontinuous selections.

[Annabel]

I am having the same issue (amongst a whole host of other issues with Excel 2007). However, my spreadsheet has about 130,000+ rows and about 130 columns, and growing. I can't sort the data, as my calculations then take forever to calculate, and my data needs to stay in the same sort order. It is very time-consuming to come and paste 25,000 rows at a time - is there a solution to this issue that Microsoft is working on?

[Kavanagh]

Twist on the same error message. I have a spreadsheet using approximately 630k cells. It has been the same relative size for several months. This week after I apply one or two filters I get this error when simply trying to use find/replace. I think it is quite a contraddiction having so many line items in excel 2007 (compared to the 65k of excel 2003) and only 25k as limit. It takes me 45 min. to sort my data and I have tons. I will try to work on files using access.