PDA

View Full Version : [SOLVED] A test to see if an entire range is empty



AJS
10-04-2004, 05:39 PM
Hi,

I would like to be able to run a test on a range to see if it consists entirely of empty cells. My code is similar to below:


Sub TestRange()
Dim CheckRange As Range
Set CheckRange = Sheet1.Range("B2:B5")
If Not IsEmpty(CheckRange) Then
MsgBox "Range Not Empty"
Else
MsgBox "Range Empty"
End If
End Sub

I have also tried


If Not CheckRange is nothing Then

in place of the IsEmpty test. I am missing something here..

Jacob Hilderbrand
10-04-2004, 06:25 PM
IsEmpty is for variables. Try this:



Option Explicit

Sub Test()
Dim CheckRange As Range
Dim ConstantRange As Range
Dim FormulaRange As Range
Set CheckRange = Sheet1.Range("B2:B5")
On Error Resume Next
Set ConstantRange = CheckRange.SpecialCells(xlCellTypeConstants, 23)
Set FormulaRange = CheckRange.SpecialCells(xlCellTypeFormulas, 23)
On Error GoTo 0
If FormulaRange Is Nothing And ConstantRange Is Nothing Then
MsgBox "Range Is Empty"
Else
MsgBox "Range Not Empty"
End If
End Sub

AJS
10-04-2004, 09:24 PM
Thanks for that!

Jacob Hilderbrand
10-04-2004, 09:29 PM
You're Welcome

Take Care