If you filter data out of large data sets, you may have run into an issue where you can hit 8192 non-contiguous ranges, which Excel couldn't handle. While this issue has been in Excel for many versions, I can honestly say that I haven't run into it in years, as I've always sorted my data first, to avoid this issue.
To replicate the issue, try this in any version of Excel prior to 2010:
Enter the following data in a blank sheet:
- A1:Â Â Â Â Row
- B1:Â Â Â Â Index
- A2:Â Â Â Â =ROW(B2)
- B2:Â Â Â Â =IF(A1=1,2,1)
Now copy row 2 down about 20,000 rows…
Next, select row 1 and Filter your data on Column B to only show 1's
Now, select from cell A3 to the end of your data in column B and try to copy it… You'll get a message like this one…
Well guess what… that's no longer necessary! In Excel 2010, the Excel team has removed this limit. And while I haven't tested this, the new limit is apparently imposed by the memory in your computer, not a hard coded limit.
It's great to see that such a long standing bug has finally been resolved. 🙂
3 thoughts on “Excel 2010 Finally Fixes SpecialCells 8192 Limit”
Ken, In Excel 2010, if you try and fill a Range from A1..XFD1048576 ie: and entire sheet, with even a simple number, say 1, Excel warns you that it may take a long time and then tells you "Excel cannot complete the task with the available resources. Choose less data or close other applications."
So excel is obviously using memory and I don't have enough and some restrictions still remain!
I know it is 17 Billion cells +/- a few.
Maybe an excuse to upgrade my PC?
Hui,
99.9% of all sheets either contain many rows and a few columns or vice versa. It is highly unlikely for anyone to need all rows and all columns.
why do you climb a mountain ?