Using The Special Cells Range Property
The SpecialCells property allows the user to return a range with cells of certain selected properties. This shows examples of that process.
Program Code
Option Explicit
Sub SpecialCellsExample1()
' ***********************************************************
' Will Return The Row Number Of The Cell With The
' Largest Row Number In The Used Range
' ***********************************************************
Dim intLastRow As Long
intLastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
MsgBox ("Last Row Is " & intLastRow)
End Sub
Sub SpecialCellsExample2()
' ***********************************************************
' Will Return The Column Number Of The Cell With The
' Largest Column Number In The Used Range
' ***********************************************************
Dim intLastColumn As Long
intLastColumn = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
MsgBox ("Last Column Is " & intLastColumn)
End Sub
Sub SpecialCellsExample3()
Dim rngBlankCells As Range
' ***********************************************************
' Will Return The Empty Cell Ranges
' Within The Defined Range
' ***********************************************************
Set rngBlankCells = Range(Cells(1, "A"), Cells(16, "A")).SpecialCells(xlCellTypeBlanks)
MsgBox (rngBlankCells.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False))
End Sub
Sub SpecialCellsExample4()
Dim rngCells As Range
' ***********************************************************
' Will Return Cell Ranges:
' (1) Text
' (2) Logical
' (3) Numeric
' (4) All Text, Logical, and Numeric
' Within The Defined Range
' It Will NOT Include Formulas
' ***********************************************************
Set rngCells = Range(Cells(1, "A"), Cells(16, "A")).SpecialCells(xlCellTypeConstants, xlTextValues)
MsgBox (rngCells.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False))
Set rngCells = Range(Cells(1, "A"), Cells(16, "A")).SpecialCells(xlCellTypeConstants, xlLogical)
MsgBox (rngCells.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False))
Set rngCells = Range(Cells(1, "A"), Cells(16, "A")).SpecialCells(xlCellTypeConstants, xlNumbers)
MsgBox (rngCells.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False))
Set rngCells = Range(Cells(1, "A"), Cells(16, "A")).SpecialCells(xlCellTypeConstants)
MsgBox (rngCells.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False))
End Sub
' ***********************************************************
' These Are Special Cell Characterists That Can Be Queried
' ***********************************************************
'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
'XlSpecialCellsValue That Can Be Used As the 2nd Parameter
'xlErrors
'xlLogical
'xlNumbers
'xlTextValues
