VBA- How to select the range of non-null cells

Strangely there's no easy way to select non-blank cells directly although there is a way to select blank cells. However, this simple piece of code selects all formulae and constants in the current selection.

Sub SelectNonBlanks()
Dim rngeAllCells As Range, rngeFormulas As Range, rngeConstants As Range

Set rngeAllCells = Selection
Set rngeFormulas = rngeAllCells.SpecialCells(xlCellTypeFormulas)
Set rngeConstants = rngeAllCells.SpecialCells(xlCellTypeConstants)
Set rngeAllCells = Union(rngeFormulas, rngeConstants)
rngeAllCells.Select

End Sub

Hope it helps,
Daniel.

Hello,

This altered code allows for the fact that there may only be constants or formulae or nothing.

Sub SelectNonBlanks()
Dim rngeAllCells As Range, rngeFormulas As Range, rngeConstants As Range
On Error Resume Next
Set rngeAllCells = Selection
If rngeAllCells Is Nothing Then Exit Sub

Set rngeFormulas = rngeAllCells.SpecialCells(xlCellTypeFormulas)
Set rngeConstants = rngeAllCells.SpecialCells(xlCellTypeConstants)

If rngeFormulas Is Nothing Then
If rngeConstants Is Nothing Then Exit Sub
rngeConstants.Select
ElseIf rngeConstants Is Nothing Then
If rngeFormulas Is Nothing Then Exit Sub
rngeFormulas.Select
Else
Set rngeAllCells = Union(rngeFormulas, rngeConstants)
rngeAllCells.Select
End If
End Sub

Regards,
Daniel.

Dank - That's great code, thanks for the reply. That actually is going to help me out a great deal with another project.

It looks like I actually didn't word this correctly in my original post - sorry about that. All I am really needing is the command that finds the last used row. I seem to recall that this is fairly easy, but I can't seem to find it on the forum anywhere.

The last row in the used range :-

Sub Find_Last_UsedRow()
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange _
.Cells.SpecialCells(xlCellTypeLastCell).Row
End Sub

The last row containing data :-

Sub Find_Last_DataRow_()
Dim LastRow As Long
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
End Sub

Perfect. Thanks!