VBA – 11. Special Cells

The syntax for the SpecialCells Method is;
expression.SpecialCells(Type, Value)

TYPE IS BELOW:
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
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells

VALUE COULD BE ANY OF FOLLOWINGS
xlErrors
xlLogical
xlNumbers
xlTextValues

Read only numbers from a Range and copy
Range(“e1:e20”).SpecialCells(xlCellTypeConstants, xlNumbers).Copy Destination:=Range(“m2”)
or
set destRange:= Range(“j2”)
for each c in range (“A1:A9″).specialcells(xlcelltypeconstants, xlnumbers)
c.copy destination:= destRange
set destRange= destRange.offset(c.rows.count)

”for textvalues, use specialcells(xlcelltypeconstants, xltextvalues)

copy formulas from a Range and copy
For Each c In Range(“b2:b12”).SpecialCells(xlCellTypeFormulas)

copy numbers and paste it in a new workbook sheet

Set wb = Workbooks.Add
Set destRange = ActiveSheet.Range(“j2”)

For Each c In Range(“b2:b12”).SpecialCells(xlCellTypeconstants, xlnumbers)

c.Copy Destination:=destRange
Set destRange = destRange.Offset(c.Rows.Count)
Next c

copy formulas with error
For Each c In Range(“b2:b12”).SpecialCells(xlCellTypeFormulas, xlErrors)

highlisht blanks cells in red
Range(“e1:e20”).SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed

delete where there is blank entry in any row
Range(“A1:E14”).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

copy visible cells to new sheet
Dim ar As Variant
ar = Array(“Due Diligence”, “Document Review”, “Others”)
With r
.AutoFilter field:=1, Criteria1:=ar, Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).Copy
Dim wb As Workbook
Set wb = Workbooks.Add
ActiveSheet.Paste
End With

Leave a Reply