Filtered data range reference is too complex

[Tony7659]

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 Devenshire]

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.

 

[Tony7659]

Shane,
It worked great. You are a star! Thanks.

Tony.

 

[Bagia]

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

 

[BobTheDrinker]

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.

 

[Kyle R]

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.

 

[Donberti]

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.

 

[nareb.postmaster]

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

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