What is the difference between .Value = "" and .ClearContents?

From what I have found, if your goal is to simple have an empty cell and you do not want to change anything about the formatting, you should use Value = vbNullString as that is the most efficient.

The 'ClearContents' is checking and changing other properties in the cell such as formatting and the formula (which is technically a separate property than Value). When using Value = "" you are only changing one property and so it is faster. Using vbNullString prompts the compiler that you are using an empty string versus the other way with double quotes, it is expecting a general string. Because vbNullString prompts it to expect an empty string, it is able to skip some steps and you get a performance gain.

You can notice a big difference in Excel spreadsheet.

Assume that B1 is filled by equation returns blankA1 = 5 B1 = "=if(A1=5,"","x")

In this case, you have to equations that you can write in C1(1) C1 = <=isblank(B1)>(2) C1 =

Solution 1 will return false, as the cell is filled with equationSolution 2 will return True

I came across this topic a little late, but i would like to share what i have noticed with abit of code of mine, i don't think i can fully explain it but ill do my best.

For Each Cell In ws.Range("D12:D161") 'Order feed colomSelect Case Cell.Value    Case 0        Cell.Interior.Color = Cell.Offset(0, -1).Interior.Color    Case 1        Cell.Interior.Color = 10198015    Case 2        Cell.Interior.Color = 11854022End SelectCell.value = "" Next Cell

This is a bit of code that i have used in order to clear some fields and give some color to the range D12:D161. Nothing special here, If the value is 0 then copy your neighbor if 1 then red if 2 then green. And clear the cell afterwards

But in order for this code to run it took roughly 5-6 seconds for me, which i thought was a fair bit for a small piece of code. Plus i used this on a Private Sub Workbook_SheetActivate(ByVal Sh As Object) which made it for the user unpleasant to wait 5-6 seconds for a screen transition. So i put a loop in to check for empty's in a row and then skip out.

It is noteworthy that this is part of a script, and yes i have my screenupdating off, calculations off, events off during this piece of code.

For Each Cell In ws.Range("D12:D161") 'Order feed colomSelect Case Cell.Value    Case 0        Cell.Interior.Color = Cell.Offset(0, -1).Interior.Color        Erow = Erow +1    Case 1        Cell.Interior.Color = 10198015        Erow = 0    Case 2        Cell.Interior.Color = 11854022        Erow = 0End SelectCell.value = ""if Erow = 10 then exit forNext Cell

Now instead of having to do 149 rows i did roughly 58 rows, depending on my data in the column. But still it it took 3-4 seconds in order to fully run. During Debug mode i noticed no lag at all. If i manually ran the code when already on the sheet, there was 0 delay. Almost instant, after testing abit more but when using a Private Sub Workbook_SheetActivate(ByVal Sh As Object) with this code it still ran 3-4 seconds.After testing individual rows of code, i came across the .Value = "". Removing this line from the code made it run 0,5 seconds.... So now i knew where my problem was, using multiple ways of emptying my cells. I noticed that .clearcontents was the fastest for me. Apparently if you move from Sheet to sheet EVENTHOUGH ws. has been declared as my active sheet, it just ook alot of time

For Each Cell In ws.Range("D12:D161") 'Order feed colomSelect Case Cell.Value    Case 0        Cell.Interior.Color = Cell.Offset(0, -1).Interior.Color        Erow = Erow +1    Case 1        Cell.Interior.Color = 10198015        Erow = 0    Case 2        Cell.Interior.Color = 11854022        Erow = 0End SelectCell.ClearContents 'DONT USE .Value = "", makes the code run slowif Erow = 10 then exit forNext Cell

In conclusiong.Using the above code with.value = "" took 4-5 seconds.value = VbNullstring took 3-4 seconds.ClearContents took only 0,5 seconds. But only during a worksheet transitionPrivate Sub Workbook_SheetActivate(ByVal Sh As Object)

If anybody is able to explain why this is or what exactly is going on, i would appreciate it.