CODEWITHSUNDEEP
vbaexcel
barciewicz
barciewicz

Reputation: 3773

Strange case when cells seem to be empty, but are not

I have some supposedly empty cells in my worksheet that are causing me trouble.

Before running any code, the output for both IsNull and IsEmpty function for any of the cells is False.

However, the following code will surprisingly work for any of the cells, even though it is looking for empty cell:

If cell.value = "" Then cell.value = "Unassigned"

What is even more strange, if I try to reverse this code:

If cell.value = "Unassigned" Then cell.value = ""

, the output for IsNull on the cell will still be False, but IsEmpty will turn into True.

This is causing me a lot of trouble in the bigger picture, because I am using a pivot table to sum up the data including those troublesome cells, and before running those two lines of code the pivot will not assign any label to those blank cells, but after running the code it will use "(blanks)" label instead. I need the naming to stay consistent.

Upvotes: 1

Views: 251

Answers (1)

Sam
Sam

Reputation: 5721

Consider this:

Debug.Print IsNull("X")
False
Debug.Print IsNull("")
False
Debug.Print IsNull(Empty)
False
Debug.Print IsNull(Null)
True

IsNull will only return true for Null. It is not suitable for cell value tests.

Upvotes: 5

Related Questions