Any difference between .value="" and .clearcontents

[Emily]

Is there any difference between .value="" and .clearcontents? Which one is better to clear the cells contents?

Thanks

 

[ChrisUK]

I've run a couple of tests and was surprised at the results.

1. Blank sheet with just the following VBA code saved was 15.5K

Sub clear()
Set rng = Range("a1:a1000")
For Each c In rng
c.Value = ""
Next
End Sub

2. Ran the code and saved the sheet, now 19K.

3. changed the code so the c.value line now reads c.clearcontents, ran this saved the sheet - result 19K!

It seems Excel is similar to Access in this way, removing a value does not free space and you'd need to compact it somehow.

My prefference would always be to clearcontents as I would have thought it would remove any references to cells from the file. Maybe this will be corrected in a later version of Excel (I'm running 2000)

Hope this helps a bit

 

[Emily]

Thanks. I found the difference using IsEmpty Function.

Sub test()
Dim MyVar, MyCheck
MyCheck = IsEmpty(MyVar) ' Return True
MyVar = Cells(1, 1) ' Cells(1, 1) = ""
MyCheck = IsEmpty(MyVar) ' Return False
Cells(1, 2).ClearContents
MyVar = Cells(1, 2)
MyCheck = IsEmpty(MyVar) ' Return True
End Sub