Printable View
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:
The macro tells me the range is NOT EMPTY every time.Code:If IsEmpty(Range("Range1:Range1_End")) Then
MsgBox "Empty Range"
Else
MsgBox "Not Empty"
End If
How would I go about checking to see if it's empty if it tells me it's always populated?
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):
Code: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
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.
Though I think it would be easier to have one range variable refer to the entire range:Code:If Application.CountBlank(Range(Range1, Range1_End)) = Range(Range1, Range1_End).Cells.Count Then
MsgBox "Empty Range"
Else
MsgBox "Not Empty"
End If
And please, keep asking questions as you have them :)Code: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
Matt
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.
MattCode:If Application.CountA(Range(Range1, Range1_End)) = 0 Then
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)
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... :doh:Quote:
Originally Posted by 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?
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 :PQuote:
Originally Posted by vonpookie
But there sometimes is - see this article http://www.vbaexpress.com/forum/arti...ticle&artid=57 :)Quote:
Originally Posted by mvidas
tonyrosen, I have renamed your thread title. Please try to keep them meaningful as to help those who wish to help you. :)
zack and john: when i went to that article, though I could read it, the top of the page had this: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. :yesQuote:
Originally Posted by http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=57
It is not just coding pickiness, it is the result of evolution of the product and trying to maintain backwards compatibility.Quote:
Originally Posted by vonpookie
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
And, WorksheetFunction is faster, by an order of circa 20%.Code: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
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.Quote:
Originally Posted by xld
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!
Okay, I used the mvidas code below:
And, if the count came back as NOT empty:Code: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, all is well in my world this morning :)Code:If IsEmpty(Range("YESNO")) Then
MsgBox("You must select either Yes or No from the dropdown")
Range("YESNO").Activate
End If