It’s a bit frustrating if you have to select any NoBlank cells (both xlCellTypeConstants and xlCellTypeFormulas) using the SpecialCells Method, as there is not one constant that handles both of them, and it goes throwing errors if any range Is Nothing. So here is the code to properly select no blank cells. You can also combine with Selection range and with formated cells to refine more the selection.
Public Function fSelectionNoBlanks() As Excel.Range
Dim oWsh As Excel.Worksheet
Dim rgCellTypeFormulas As Excel.Range
Dim rgCellTypeConstants As Excel.Range
Dim rgCellTypeFormat As Excel.Range
Dim rgSelection As Excel.Range
Dim rgFormated As Excel.Range
Dim oCells As Excel.Range
Dim oCell As Excel.Range
Dim bFormated As Boolean
Set rgSelection = Selection
Set oWsh = ActiveSheet
With oWsh
‘Selecting hardcoded data and formulas
On Error Resume Next
Set rgCellTypeConstants = .Cells.SpecialCells(xlCellTypeConstants)
Set rgCellTypeFormulas = .Cells.SpecialCells(xlCellTypeFormulas)
‘Set rgCellTypeFormat = .Cells.SpecialCells(xlCellTypeAllFormatConditions)
On Error GoTo 0
If rgCellTypeFormulas Is Nothing And Not rgCellTypeConstants Is Nothing Then
Set oCells = rgCellTypeConstants
ElseIf rgCellTypeConstants Is Nothing And Not rgCellTypeFormulas Is Nothing Then
Set oCells = rgCellTypeFormulas
ElseIf Not rgCellTypeConstants Is Nothing And Not rgCellTypeFormulas Is Nothing Then
Set oCells = Union(rgCellTypeConstants, rgCellTypeFormulas)
End If
‘Set oCells = Intersect(oCells, rgSelection)
If Not oCells Is Nothing Then
fSelectionNoBlanks = oCells
‘oCells.Select
‘For Each oCell In oCells
‘Next oCell
End If
End With
End Function