Error "Microsoft office excel cannot create or use the data range reference because it is too complex"

[pedie]

Hi 'm getting this error message "Microsoft office excel cannot create or use the data range reference because it is too complex" when i try to delete/copy/cut/select chunk of data...

Can someone please help me with this get rid of this error messange and work this out?

I have data of more then 557000 row....4 cols...

Please advice.

 

[MrKowz]

Thanks...thats much better MrKows! I did turn off screen updating but didnt take care of calculation part...

Can you also please help me with my post#8?

You're going to need to provide much more clear details for that.

 

[pedie]

Thanks again.

Okay lets say I have huge data rowccount is =550000
I want this 550000/4 and then divide whole data into 4 groups and then cut each group and paste it in new sheet [lets day 'tempsheet'] then filter the data by "=MyIndex No:*" then copy this to this sheet f col. again filter it by "=Data Xi No*" and paste it in G col...

And follow same steps for each group divided...

Is this how we break down data?

 

[MrKowz]

Thanks again.

Okay lets say I have huge data rowccount is =550000
I want this 550000/4 and then divide whole data into 4 groups and then cut each group and paste it in new sheet [lets day 'tempsheet'] then filter the data by "=MyIndex No:*" then copy this to this sheet f col. again filter it by "=Data Xi No*" and paste it in G col...

And follow same steps for each group divided...

Is this how we break down data?

What I meant in the earlier post about breaking down data was if you were selecting/cutting/copying/pasting the data manually. When it comes to code, there is absolutely no need to break apart the data.

 

[pedie]

Okay thanks...
But now that we have initiated that part of the conversation can you pls write one for me if that wont take too much of your time?

 

[vma2084]

Hi All,

I am facing an error while copy data from 3 files into 1 file.


-- removed inline image ---


{
Microsoft Office Excel Cannot Create or Use the Data Range reference because its 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

[OK]
}

To explain the problem I have attached a sample of the 3 files which I have been working on in the below link. I have to filter for a certain condition mentioned below, post which the resultant should be copied into a new file into 3 respective tabs and also combine all of them into the 4th tab.

Now when I copy them into the respective tab the Second file copies with no problem file, while the 1st and the 3rd file faces problems by popping up the above error and the whole data gets copied

Files Names with the Filter Condition in Bracket
File 1.CSV (DeptID = 12) - contains 19725 Rows
File 2.CSV (DeptID = 30) - contains 9096 Rows
File 3.CSV (DeptID = 12) - contains 84513 Rows

Files are located in the below link :wink:
http://www.esnips.com/web/vma2084ExcelFiles/

Could you please help me on the above, I am think if there is a way to copy the filtered data part by part.

Thanks alot in advance

Regards,
VMA