Check if Range is Empty

[tonyrosen]

Mainly because I'm about complete with this.

Let's say we have a range defined in the worksheet as: Range 1
and, the end of said range would also be defined : Range1_End

I'm wanting to check to see if the cell(s) are empty.

Whenever I use:

If IsEmpty(Range("Range1:Range1_End")) Then
    MsgBox "Empty Range"
Else
    MsgBox "Not Empty"
End If

The macro tells me the range is NOT EMPTY every time.

How would I go about checking to see if it's empty if it tells me it's always populated?

[vonpookie]

One possibility is to use the COUNTA function. This counts the number of cells in the specified range that are not blank (if the entire range is blank, it will return a 0):

Sub test()
    Dim count As Long
    count = WorksheetFunction.CountA(Range("Range1:Range1_End"))
    If count = 0 Then
        MsgBox "Empty Range"
    Else
        MsgBox "Not Empty"
    End If
End Sub

[mvidas]

Hi Tony,

IsEmpty is really meant to tell whether a variable has been initialized or not. A way to check if all cells in a range is empty is to compare the COUNTBLANK return value to the cell count.

If Application.CountBlank(Range(Range1, Range1_End)) = Range(Range1, Range1_End).Cells.Count Then
    MsgBox "Empty Range"
Else
    MsgBox "Not Empty"
End If

Though I think it would be easier to have one range variable refer to the entire range:

Dim RG As Range
Set RG = Range(Range1, Range1_End)
If Application.CountBlank(RG) = RG.Cells.Count Then
    MsgBox "Empty Range"
Else
    MsgBox "Not Empty"
End If

And please, keep asking questions as you have them
Matt

[mvidas]

Good point vonpookie, I often forget about counta

Also, you don't need the "WorksheetFunction" part in there, as it is unnecessary and can sometimes lead to errors that shouldn't be there.

If Application.CountA(Range(Range1, Range1_End)) = 0 Then

Matt

[tonyrosen]

Counting gives me everything is empty ... so, I'm going to work from there and see what I come up with (as, I only need to know if any cell in the range has stuff in it)

[vonpookie]

Also, you don't need the "WorksheetFunction" part in there, as it is unnecessary and can sometimes lead to errors that shouldn't be there.

Ah. I have a habit of using that, after too many times of trying things where it wouldn't work *without* it. Goofy Excel and it's coding pickiness...

[mvidas]

What are your Range1 and Range1_End variables Dim'med as, and how are you setting them? Can you post either your whole code or just the Dim statements and where you're setting the values?

[mvidas]

Ah. I have a habit of using that, after too many times of trying things where it wouldn't work *without* it. Goofy Excel and it's coding pickiness...

What I love about it is when you type Application. and the list of properties/methods comes up, you wont see a single worksheet function listed, though they all work! I don't think there is an output difference, unless you're dealing with arrays, so I just never use the wsf :P

[johnske]

What I love about it is when you type Application. and the list of properties/methods comes up, you wont see a single worksheet function listed, though they all work! I don't think there is an output difference, unless you're dealing with arrays, so I just never use the wsf :P

But there sometimes is - see this article http://www.vbaexpress.com/forum/arti...ticle&artid=57

[Zack Barresse]

tonyrosen, I have renamed your thread title. Please try to keep them meaningful as to help those who wish to help you.

[mvidas]

zack and john: when i went to that article, though I could read it, the top of the page had this:

Warning: readdir(): supplied argument is not a valid Directory resource in /articles.php on line 1334

Warning: closedir(): supplied argument is not a valid Directory resource in /articles.php on line 1346
((vbax logo, everything fine from here and below))

I know, I saw it too. I'll try to get on that one. It doesn't seem to affect anything but is more of a nusance than anything else. Thanks Matt.

[Bob Phillips]

Ah. I have a habit of using that, after too many times of trying things where it wouldn't work *without* it. Goofy Excel and it's coding pickiness...

It is not just coding pickiness, it is the result of evolution of the product and trying to maintain backwards compatibility.

Both methods (Application and WorksheetFunction) will work. Application was the pre Excel 97 way of doing it and is retained for compatibility, but
there are some nuances in the way they work.

There were problems with some functions in Excel97 and 2000 using the WorksheetFunction method, which did not surface using Application.

WorksheetFunction (and Application.WorksheetFunction) supports the "auto list members" option, whereas Application alone does not. In Excel 97 it was necessary to use Application.WorksheetFunction to get this auto list, but in later versions WorksheetFunction alone works.

Not all worksheet functions are exposed to VBA. Functions not exposed by the WorksheetFunction object usually have a VBA equivalent (e.g., Left, Year), but they do not necessarily work in exactly the same way.

Functions within Add-ins, such as the Analysis Toolpak, cannot be called with Application or WorksheetFunction. Errors are handled differently. When a function is called directly using Application (e.g., Application.VLookup) , the result is a variant containing an error. When called directly using WorksheetFunction (e.g., WorksheetFunction .*VLookup), the function will raise an error. Both can be managed, but in different ways

Dim ans As String
On Error Resume Next
ans = WorksheetFunction.VLookup("value", table_array, 2, False)
On Error GoTo 0
If ans = "" Then
    MsgBox "not found using WorksheetFunction"End If
If IsError(Application.VLookup("value", table_array, 2, False)) Then
    MsgBox "not found using Application"
End If

And, WorksheetFunction is faster, by an order of circa 20%.

[mvidas]

<<lots of good information...>>

And, WorksheetFunction is faster, by an order of circa 20%.

Its strange.. I did some time trials about a year back with a few different functions, and with the ones I tested I got about the same runtime, with non-WSF being a little faster. But I just tested a couple others, and non-WSF was slower.

I didnt change versions, or even computers! how weird. I do have xl2000, and have come across a couple errors using WSF, so I use non-WSF out of habit mostly. I rarely even have to use a function like this, but next time I do I'll definately be testing the runtime. I can only believe the numbers (this time).

Good info there!

[tonyrosen]

Okay, I used the mvidas code below:

Dim RG As Range 
Set RG = Range("Range1:Range1_End") 
If Application.CountBlank(RG) = RG.Cells.Count Then 
    MsgBox "Empty Range" 
Else 
    MsgBox "Not Empty" 
End If

And, if the count came back as NOT empty:

If IsEmpty(Range("YESNO")) Then
    MsgBox("You must select either Yes or No from the dropdown")
    Range("YESNO").Activate
End If

And, all is well in my world this morning