Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtered data error. Need to go to VBA?
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. Interesting enough, if I copy down about 14,000 rows, it works. Likewise, it works if I copy from bottom up about 16,000 rows. Do I have to use code so only selected rows show? Well, I am not that familiar with that. Any help? Thanks. Tony. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtered data error. Need to go to VBA?
Question answered by Shane Devenshire. Thank you!
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. Interesting enough, if I copy down about 14,000 rows, it works. Likewise, it works if I copy from bottom up about 16,000 rows. Do I have to use code so only selected rows show? Well, I am not that familiar with that. Any help? Thanks. Tony. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error in formula after auto-filtered | Excel Discussion (Misc queries) | |||
Filtered Mode Copying and pasting as value yield as error? | Excel Discussion (Misc queries) | |||
Filtered copy and paste error | Excel Discussion (Misc queries) | |||
Return filtered values into report worksheet based on filtered valueon the data worksheet | Excel Worksheet Functions | |||
Help to sort out filtered data from the data contained in another sheet of the same workbook | Excel Worksheet Functions |