Excel 2010 Finally Fixes SpecialCells 8192 Limit

Posted on July 23rd, 2009 in Excel, Office 2010 by Ken Puls

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 Comments