 |
|
|
|
|
|
Got any Excel Questions? Excel Help .
The SpecialCells
Method in Excel VBA
One of the most beneficial Methods in Excel (in my experience) is the
SpecialCells Method. When used, it returns a Range Object that represents
only those type of cells we specify. For example, one can use the SpecialCells
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;
expression.SpecialCells(Type, Value)
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.
xlErrors
xlLogical
xlNumbers
xlTextValues
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.
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
If we wanted, we could narrow this down further to only return a Range Object representative of all formulae that are returning numbers.
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas,xlNumbers)
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 PasteSpecial 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
SpecialCells Gotcha!
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.
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS