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. 🙂