A Dangerous SpecialCells Bug
The SpecialCells method is a handy way to work with a subset of cells. But using this method doesn't always work as it should.
Macro1, listed below, works with range C1:C16385. When executed, the macro selects all cells within this range that contain a formula which returns an error (for example, #DIV/0!). It works just fine.
Sub Macro1()
Range("C1:C16385").SpecialCells _
(xlCellTypeFormulas, xlErrors).Select
End Sub
Now look at Macro2. The only difference is that the macro works with range C1:C16386 (i.e., one additional cell). This macro does not work as expected. Run it, and you'll see that it selects all cells in the range.
Sub Macro2()
Range("C1:C16386").SpecialCells _
(xlCellTypeFormulas, xlErrors).Select
End Sub
I haven't bothered to experiment with this to find the exact conditions that trigger the bug, but the problem seems to occur with any range that exceeds 16,385 rows.
No big deal? Well, keep in mind that your code could be using the Delete method instead of the Select method.
By the way, I've been using VBA for a long time, and I had never encountered this problem. Just luck, I guess.
Submitted by Bob Umlas.