Any difference between .value="" and .clearcontents

Emily

Active Member
Joined
Aug 28, 2002
Messages
304
Is there any difference between .value="" and .clearcontents? Which one is better to clear the cells contents?

Thanks
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
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

Active Member
Joined
Aug 28, 2002
Messages
304
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,381
Members
412,589
Latest member
ArtBOM
Top