isempty range

V

vbaNewby

Board Regular
Joined
Jan 26, 2011
Messages
138
HI I have the following:

Code: 
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?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
You need to loop through the range to check for data.

Code: 
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
 
Upvote 0
try this

Code: 
Set myRange = Range("A1:D48")
If IsEmpty(myRange) Then
   MsgBox "myRange has contents"
Else
   MsgBox "myRange does not have contents"
End If
 
Upvote 0
MrKowz said:
You need to loop through the range to check for data.

Code: 
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
Click to expand...
Thanks MrKowz, I am giving this a try right now.
 
Upvote 0
texasalynn said:
try this

Code: 
Set myRange = Range("A1:D48")
If IsEmpty(myRange) Then
   MsgBox "myRange has contents"
Else
   MsgBox "myRange does not have contents"
End If
Click to expand...
HI texas thanks for reply. I've actually tried this and it didn't work.


Code: 
Set myRange = Range("A1:D48")
If not (IsEmpty(myRange)) Then
   MsgBox "myRange does not have contents"
Else
   MsgBox "myRange has contents"
End If
 
Upvote 0
mirabeau said:
Actually, you don't need to loop thru' the range. It's one way of doing it tho' and certainly nothing wrong with it.
Click to expand...

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

Code: 
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.
 
Upvote 0
vbaNewby said:
Okay, so how is done without looping? Curious.
Click to expand...
Here's a few other non-looping codes, shouldn't he hard to think up more if need be
Code: 
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
Code: 
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
Code: 
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
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Exclude a row when searching table values
Replies
3
Views
336
mumps
mumps
R
  • Question Question
Can you help with this code please?
Replies
3
Views
372
rlv01
R
I
  • Question Question
Open selected customer in my main database
Replies
2
Views
345
ipbr21054
I
I
  • Question Question
Advice for conditional formatting not working
Replies
1
Views
338
rlv01
R
J
  • Question Question
Combox is halting my Workbook
Replies
1
Views
211
rlv01
R

Forum statistics

Threads
1,224,599
Messages
6,179,831
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top