Excel 2010 Finally Fixes SpecialCells 8192 Limit

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

  1. 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?

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *