isempty range

[vbaNewby]

HI I have the following:

Set myRange= Range("A1:D48")
If Not (IsEmpty(myRange)) Then
   msgbox "myRange has contents"
else
   msgbox "myRange does not have contents

Everytime I run it I get "myRange has contents"

I can assure you that there is no data in the cells. Am I doing this correct?

 

[MrKowz]

You need to loop through the range to check for data.

Public Sub foo()
Dim myRange As Range, _
    rng     As Range, _
    rngbool As Boolean
    
Set myRange = Range("A1:D48")
rngbool = False
For Each rng In myRange
    If rng.Value <> "" Then
       rngbool = True
       Exit For
    End If
Next rng
MsgBox "MyRange " & IIf(rngbool, "has contents", "does not have contents.")
End Sub

 

[texasalynn]

try this

Set myRange = Range("A1:D48")
If IsEmpty(myRange) Then
   MsgBox "myRange has contents"
Else
   MsgBox "myRange does not have contents"
End If

 

[vbaNewby]

You need to loop through the range to check for data.

Public Sub foo()
Dim myRange As Range, _
    rng     As Range, _
    rngbool As Boolean
    
Set myRange = Range("A1:D48")
rngbool = False
For Each rng In myRange
    If rng.Value <> "" Then
       rngbool = True
       Exit For
    End If
Next rng
MsgBox "MyRange " & IIf(rngbool, "has contents", "does not have contents.")
End Sub

Thanks MrKowz, I am giving this a try right now.

 

[vbaNewby]

try this

Set myRange = Range("A1:D48")
If IsEmpty(myRange) Then
   MsgBox "myRange has contents"
Else
   MsgBox "myRange does not have contents"
End If

HI texas thanks for reply. I've actually tried this and it didn't work.

Set myRange = Range("A1:D48")
If not (IsEmpty(myRange)) Then
   MsgBox "myRange does not have contents"
Else
   MsgBox "myRange has contents"
End If

 

[vbaNewby]

MrKowz, Thank you sir! Works well.

 

[mirabeau]

You need to loop through the range to check for data.

Actually, you don't need to loop thru' the range. It's one way of doing it tho' and certainly nothing wrong with it.

 

[vbaNewby]

Actually, you don't need to loop thru' the range. It's one way of doing it tho' and certainly nothing wrong with it.

Okay, so how is done without looping? Curious.

 

[MrKowz]

Actually, you don't need to loop thru' the range. It's one way of doing it tho' and certainly nothing wrong with it.

You're right - a quicker, way to test for truly empty cells (cells that do not contain a formula OR value) would be to just use

Application.Counta(MyRange)

However, if you have any cells with a formula or a value in that range, even if the formula returns a blank, this will fail.

Or, alternatively, if the values that WOULD be in that range were all numeric, you could test for if the SUM of that range is 0.

 

[mirabeau]

Okay, so how is done without looping? Curious.

Here's a few other non-looping codes, shouldn't he hard to think up more if need be

Sub blankranges1()
Dim myRange As Range, fnd As Object
Set myRange = Range("A1:D48")
Set fnd = myRange.Find("*")
If fnd Is Nothing Then MsgBox "does not have contents" Else MsgBox "myRange has contents"
End Sub

Sub blankranges2()
Dim myRange As Range
Set myRange = Range("A1:D48")
If myRange.SpecialCells(4).Count = myRange.Cells.Count Then _
    MsgBox "does not have contents" Else MsgBox "myRange has contents"
End Sub

Sub blankranges3()
Set myRange = Range("A1:D48")
If myRange.Cells.Count = Application.CountBlank(myRange) Then _
    MsgBox "does not have contents" Else MsgBox "myRange has contents"
End Sub

Sub blankranges4()
Dim answ As Long
Range("A1:D48").Name = "myRange"
answ = Evaluate("=sum(if(myRange ="""", 0, 1))")
If answ = 0 Then MsgBox "does not have contents" Else MsgBox "myRange has contents"
End Sub