difference between isempty(range("A1")) and range("A1") = ""

[Pookiemeister]

what's the difference between isempty(range("A2")) vs if range("A2") = ""

 

[MARK858]

ISEMPTY tests if a cell is empty (the same as the formula =ISBLANK), ="" will test if the cell is empty or has a formula returning ""

 

[Tetra201]

IsEmpty only returns meaningful information for variants.

 

[Pookiemeister]

So if I understand both of you correctly, if I wanted to check if a cell to make sure there is nothing inside it or assigned to that cell (like a formula) I would you the ISEMPTY? Is that right? I was also told that ISEMPTY is more "powerful" (whatever that means) than just using "If Range("Letter#") = " " Then". Thank you.

 

[Tetra201]

IsEmpty is intended to indicate whether a variant variable has been initialized. It returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False.

You can experiment yourself with using IsEmpty for ranges; however, here is the difference:

Dim a As Variant ' IsEmpty(a) returns True
a = Empty ' IsEmpty(a) returns True
a = "" ' IsEmpty(a) returns False
'
Range("A1") = Empty ' IsEmpty(Range("A1")) returns True
Range("A1") = "" ' IsEmpty(Range("A1")) returns True

 

[Alex Blakenburg]

what's the difference between isempty(range("A2")) vs if range("A2") = ""

Using your wording:
1) isempty(range("A2"))
a) If the cell is totally empty isempty will return True.
b) If the cells contains an empty string ie "" which is commonly the case when using formulas then isempty will return False
c) If you copy paste a cell that contained an empty string "" even though you won't be able to see anything isempty on the pasted cell will return False
Note: For c), If you use ctrl+up or ctrl+down arrow you will find it will behave as though the cell contains a value.
(The Excel ISBLANK behaves the same way)

2) range("A2") = ""
Since in most cases you consider a cell to be empty both if it empty and if it contains an empty string.
using = "" returns True for both those scenarios.
So this is generally the safer option.
Within Excel you will find that = "" is used more often than IsBlank for the same reason.

I found @Tetra201's last line to be a bit misleading because if you use the code below IsEmpty will return True.
This is not because IsEmpty("") is returning True but because when you put an empty string into a cell using VBA it actually converts it to Empty.

Range("A1") = ""
Debug.Print IsEmpty(Range("A1")   ' Returns True because it will make A1 empty not ""

In fact one of the ways to convert all the empty strings to actually being empty after copying a sheet as paste values in to rely on the abovementioned conversion and use.

With ActiveSheet.UsedRange
    .Value = .Value
End With

ok ok, in short what @MARK858 said