I’ve written a lot on this blog about
Range.SpecialCells() and how careful you have to be when using it. Here’s yet another caveat which I didn’t know about until another developer brought it to my attention.
Put some data in a worksheet (to avoid runtime errors for this test) and then put this code in that worksheet’s class module:
Option Explicit Sub TestSpecialCells1() Cells.SpecialCells xlCellTypeBlanks End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) MsgBox Target.Address End Sub
If you run
TestSpecialCells1() you’ll find that, provided a blank cell was found, the
Worksheet_SelectionChange() event is raised – which is immensely annoying. The code clearly doesn’t select any cells so why should that event get raised? The answer is that the code behind
Range.SpecialCells() is shoddy.
Range.SpecialCells() mirrors using many of the options in the [F5 > Special] ‘Go To Special’ dialog box (shown below). When a user tries to access special cells via the interface it is quite reasonable that those cells are selected but, when we’re accessing them via code, it shouldn’t happen. To put it simply:
Range.SpecialCells() is too tightly coupled to the user interface:
If you test your way through these options you’ll find that
Range.SpecialCells() isn’t the only
Range class member which exhibits this behaviour. Watch out for any of these:
For some reason
Range.CurrentRegion doesn’t – which is a good thing – but note that its
Range.SpecialCells(5), does. You’ll also find some more interesting behaviour which gives us an insight as to how it works. For example:
Sub TestSpecialCells2() Cells.SpecialCells xlCellTypeLastCell End Sub
Range.SpecialCells(xlCellTypeLastCell) causes the
Worksheet_SelectionChange() event to be raised twice! Why? Once to find the last row and once to find the last column so that it can then intersect them to get the last cell.
Until Microsoft fixes this event issue (and I seriously doubt it ever will), the workaround is to set
False before using it and setting it back to
True again after using it. Proper error handling needs to be in place to ensure
Application.EnableEvents is reset correctly because a whole host of situations can cause these calls to throw a runtime error.