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.

Hi Tony,

I understand that you are facing an issue while trying to copy and paste filtered data on your worksheet. The error message you are receiving indicates that the data range reference you are trying to use is too complex for Excel to handle. However, there are a few things you can try to resolve this issue.

1. Firstly, you can try to reduce the complexity of your data range reference by selecting only the visible cells in your filtered range. To do this, select your filtered range, press F5, and then click on the Special button. In the Go To Special dialog box, select the Visible cells only option and click OK. This will select only the visible cells in your filtered range, which should be less complex than the entire range.
   
2. Once you have selected only the visible cells, you can copy and paste them as usual. If this doesn't work, you can try copying and pasting the data as values instead of formulas. To do this, select your filtered range, right-click and select Copy, then right-click again and select Paste Special. In the Paste Special dialog box, select the Values option and click OK. This will paste the data as values, which should be less complex than the original formulas.
   
3. If neither of these solutions work, you can try breaking up your data range into smaller chunks and copying and pasting them separately. For example, you could copy and paste the first 500 rows, then the next 500 rows, and so on until you have copied and pasted all 17,000 rows.

I hope these solutions help you resolve your issue. Let me know if you have any further questions or if there's anything else I can assist you with.

Best regards,
[Your Name]

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.

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.

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.

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.

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.

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.

On Monday, June 15, 2009 10:37:02 AM UTC-4, 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.

That worked thanks!!!! Been searching for hours to find a solution