If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filtered data range reference is too complex
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. Tony. |
#2
|
|||
|
|||
Filtered data range reference is too complex
Hi,
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. Cheers, Shane Devenshire "Tony7659" wrote: 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. Tony. |
#3
|
|||
|
|||
Filtered data range reference is too complex
Shane,
It worked great. You are a star! Thanks. Tony. "Shane Devenshire" wrote: Hi, 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. Cheers, Shane Devenshire "Tony7659" wrote: 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. Tony. |
#4
|
|||
|
|||
Filtered data range reference is too complex
Hi Shane,
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 discontinous selections. Thanks, Bagia "Shane Devenshire" wrote: Hi, 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. Cheers, Shane Devenshire "Tony7659" wrote: 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. Tony. |
#5
|
|||
|
|||
Filtered data range reference is too complex
Hi Shane
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? Thanks. "Bagia" wrote: Hi Shane, 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 discontinous selections. Thanks, Bagia "Shane Devenshire" wrote: Hi, 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. Cheers, Shane Devenshire "Tony7659" wrote: 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. Tony. |
#6
|
|||
|
|||
Filtered data range reference is too complex
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. "Shane Devenshire" wrote: Hi, 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. Cheers, Shane Devenshire "Tony7659" wrote: 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. Tony. |
#7
|
|||
|
|||
Filtered data range reference is too complex
Hi Shane,
nothing against stars - 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. -- Donatella "Shane Devenshire" wrote: Hi, 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. Cheers, Shane Devenshire "Tony7659" wrote: 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. Tony. |
Thread Tools | |
Display Modes | |
|
|