MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by Dan on November 08, 2001 7:38 AM

I'm trying to solve a little problem. I need the command in VBA to select the range of all cells that are not blank. I have a set number of columns, but depending on the import, the number of rows will vary. TIA.

Let me know if you need clarification.


Posted by Dank on November 08, 2001 8:42 AM

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.

Posted by Dank on November 08, 2001 8:49 AM

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.

Posted by Dan on November 08, 2001 9:06 AM

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.

Posted by Manoel Francisco Dos Santos on November 08, 2001 12:46 PM


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


Posted by Dan on November 08, 2001 1:05 PM

Perfect. Thanks!