Super secret SpecialCells

I’m sure we’re all familiar with the Range.Specialcells method. If you’re not, you need to be, because it’s incredibly useful, so go and check it out now. Go on, we’ll wait.

 

OK, now that we’re all on the same page, you will be aware that there are 2 arguments you can supply to the SpecialCells method:

  • Type (required).
    • This is, naturally, the type of cells you are interested in and the available values are shown as those of the XlCellType Enum. More on this in a minute.

     

  • Value (Optional).

    When the Type argument is xlCellTypeConstants or xlCellTypeFormulas, you can specify which type(s) of values you are interested in:

Enum name

Literal value

xlNumbers

1

xlTextValues

2

xlLogical

4

xlErrors

16

 

Note: these values are additive (like the buttons for a MsgBox call), so if you want numbers and text, you can specify xlNumbers + xlTextValues

As far as I can tell, you can supply any number between 1 and 255 for the Value argument but it will be ignored if the Type argument is not xlCellTypeFormulas or xlCellTypeConstants, or if it’s not a valid combination of the values in the table above.

 

So, back to point 1 as promised. According to the documentation, the valid Type values are those of the XlCellType enumeration, namely:

 

Name

Literal value

xlCellTypeAllFormatConditions

-4172

xlCellTypeAllValidation

-4174

xlCellTypeBlanks

4

xlCellTypeComments

-4144

xlCellTypeConstants

2

xlCellTypeFormulas

-4123

rxlCellTypeLastCell

11

xlCellTypeSameFormatConditions

-4173

xlCellTypeSameValidation

-4175

xlCellTypeVisible

12

 

However, this is not in fact a complete list of the possible values you can pass. In addition, you can pass values which correspond (roughly) to the order in which the items appear in the Goto – Special… dialog (below)

 

 

Type number

Equivalent

Same as XlCellTypeEnum value

Range.SpecialCells(1)

Range.SpecialCells(xlCellTypeComments)

No

Range.SpecialCells(2)

Range.SpecialCells(xlCellTypeConstants)

Yes

Range.SpecialCells(3)

Range.SpecialCells(xlCellTypeFormulas)

No

Range.SpecialCells(4)

Range.SpecialCells(xlCellTypeBlanks)

Yes

Range.SpecialCells(5)

Range.CurrentRegion

No enum value

Range.SpecialCells(6)

Range.CurrentArray

No enum value

Range.SpecialCells(7)

Range.RowDifferences

No enum value

Range.SpecialCells(8)

Range.ColumnDifferences

No enum value

Range.SpecialCells(9)

Range.Precedents

No enum value

Range.SpecialCells(10)

Range.Dependents

No enum value

Range.SpecialCells(11)

Range.SpecialCells(xlCellTypeLastCell)

Yes

Range.SpecialCells(12)

Range.SpecialCells(xlCellTypeVisible)

Yes

Range.SpecialCells(13)

Range.SpecialCells(xlCellTypeAllFormatConditions)

No

Range.SpecialCells(14)

Range.SpecialCells(xlCellTypeAllValidation)

No

 

Hopefully this will prove useful to someone. J

 

A few key things to note:

If you use the SpecialCells method applied to one cell, it typically works as if applied to the entire worksheet. However, this is not true if you use 5, 6, 9 or 10 as the Type argument.

Prior to Excel 2010, specialcells can return a maximum of 8192 areas (discrete blocks of cells). If more areas than that apply, the returned range will be the whole of the original range.

I can at present find no way using 7, 8, 13 or 14 to provide the additional options available in the Goto – Special dialog since the Values parameter seems to simply be ignored.

6 thoughts on “Super secret SpecialCells

    • Rory Archibald says:

      I forget now – this was another of those things that I’ve been intending to write for ages! (1 down, several hundred more to go…)

  1. Todd says:

    I have used the Range.SpecialCells(xlCellTypeComments) to highlight all cells with comments. Now that some comments are met, how do I select all cells without comments as to set their color back to white?

  2. Van Knowles says:

    I’m not convinced that SpecialCells is all that useful. In fact, it seems to require special care in coding because if the range you’re querying doesn’t have the type of cell you’re looking for, you end up with a run-time error. I suppose you could carefully check for errors and for empty (Nothing) ranges, but that seems like a lot of extra work. On the other hand, SpecialCells is very convenient for finding certain types of cells.

    For greater detail on what some of the arguments produce, I found this useful discussion: https://www.mrexcel.com/forum/excel-questions/21342-xlcelltypesameformatconditions.html

    • romperstomper says:

      I guess that’s a matter of opinion. I, like most coders I know, use it extensively. It takes no more than a simple error check to cater for nothing being returned.

Leave a Reply

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