Got any Excel/VBA Questions? Excel Help.
The Special Cells Method in Excel VBA
One of the most beneficial Methods in Excel (in my experience) is the Special Cells Method. When used, it returns a Range Object that represents only those type of cells we specify. For example, one can use the Special Cells Method to return a Range Object that only contains formulae. In fact, we can, if we wish, even narrow it down further to have our Range Object (containing only formulae) to return only formulae with errors.
The syntax for the SpecialCells Method is:
Where "expression" must be a Range Object. For example Range("A1:C100"), ActiveSheet.UsedRange etc.
Type=XlCellType and can be one of these XlCellType constants.
xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
xlCellTypeLastCell. The last cell in the used range. Note this XlCellType will include empty cells that have had any of cells default format changed.
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells
These arguments cannot be added together to return more than one XlCellType.
Value=XlSpecialCellsValue and can be one of these XlSpecialCellsValue constants.
These arguments can be added together to return more than one XlSpecialCellsValue.
The SpecialCells Method can be used in a wide variety of situations when you only need to work with cells housing specific types of data. For example, the code below would return a Range Object representing all formulae on the active Worksheet.
If we wanted, we could narrow this down further to only return a Range Object representative of all formulae that are returning numbers.
Once we have the specific Range Object type returned we can then work with only those cells. This can often be done in one line of code, or you may need to loop through the range. See examples below;
Sub ColorAllFormulae() ActiveSheet.UsedRange.SpecialCells _ (xlCellTypeFormulas).Interior.ColorIndex = 6 End Sub Sub NegativeAllNumberFormulae() Dim rRange As Range, rCell As Range Set rRange = ActiveSheet.UsedRange.SpecialCells _ (xlCellTypeFormulas, xlNumbers) For Each rCell In rRange rCell = rCell.Value * -1 Next rCell End Sub
Although I have used a loop on the second macro, so that all returned numbers are converted to their negative counterparts, we could make use of Paste Special to do so without looping and allow the formulae to remain in the cells. That is;
Sub NegativeAllNumberFormulae2() With Range("IV65536") .Value = -1 .Copy ActiveSheet.UsedRange.SpecialCells _ (xlCellTypeFormulas, xlNumbers).PasteSpecial _ xlPasteValues, xlPasteSpecialOperationMultiply .Clear End With End Sub
If you are familiar with Excel and it's built in features, such as SpecialCells, you will know that when/if one specifies only a single cell (via Selection or Range) Excel will assume you wish to work with the entire Worksheet of cells. For example, the 2 macros below would both select ALL blank cells on a Worksheet.
Sub SelectAllBlanks() ActiveSheet.UsedRange.SpecialCells _ (xlCellTypeBlanks).Select End Sub Sub SelectAllBlanks2() Range("A1").SpecialCells _ (xlCellTypeBlanks).Select End Sub
So, as you can see, specifying only a single cell Range can give unwanted results.
SpecialCells for Formulae & Constants
While we cannot specify more than one XlCellType (e.g. xlCellTypeConstants+xlCellTypeFormulas would fail) we can use the SpecialCells method to return only used cells housing numbers on a Worksheet (formulae & constants) and omit any cells containing text (formulae & constants).
Sub AllNummericCells() Dim rCcells As Range, rFcells As Range Dim rAcells As Range 'Set variable to all used cells Set rAcells = ActiveSheet.UsedRange On Error Resume Next 'In case of no numeric formula or constants. 'Set variable to all numeric constants Set rCcells = rAcells.SpecialCells(xlCellTypeConstants, xlNumbers) 'Set variable to all numeric formulas Set rFcells = rAcells.SpecialCells(xlCellTypeFormulas, xlNumbers) 'Determine which type of numeric data (formulas, constants or none) If rCcells Is Nothing And rFcells Is Nothing Then MsgBox "You Worksheet contains no numbers" End ElseIf rCcells Is Nothing Then Set rAcells = rFcells 'formulas ElseIf rFcells Is Nothing Then Set rAcells = rCcells 'constants Else Set rAcells = Application.Union(rFcells, rCcells) 'Both End If On Error GoTo 0 rAcells.Select End Sub
You should take note of the On Error Resume Next statement in the above code. This is needed as when the SpecialCells Method condition cannot be met an error occurs. As you may, or may not know, a non valid Range Object returns the Nothing keyword. After Setting a Range variable to the SpecialCells Method we need to then check that we have been able to pass a Range Object to our Range variable. It is the If Statement (and 2 ElseIf) that checks this in the code above.
|Index to Excel VBA Code|
|Sort by Color In Excel|
|Sort Alphanumeric Text|
|Sort an Array|