Thread: type mismatch on cell contents. Cell.clearcontents

Hi there,

I have a simple formula procedure which is linked to a button. When the button is clicked, nothing happens even though ranges have values.

Dim cell As Object
For Each cell In Worksheets("test").Range("F2:J40")
If cell.Value <> 0 Then cell.ClearContents 
Next cell
End Sub

Columns have values, but this button doesn't really clear them.

Is anyone able to assist?

Thanks

Instead try:

Dim ce As Range
For Each ce In Worksheets("test").Range("F2:J40")
If ce.Value <> 0 Then ce.ClearContents 
Next ce
End Sub

I usually avoid using "CELL" as a variable name. I also re-dimmed ce as a Range instead of an Object.

Just as a test, if you run

Private Sub ClearALL_Click()
Dim ce As Range
msgbox "code is running"
For Each ce In Worksheets("test").Range("F2:J40")
If ce.Value <> 0 Then ce.ClearContents
Next ce
End Sub

do you see the message?

OnError's suggestion was going to be next... are your macros enabled? Or did they somehow get disabled (via code or not enabling them when opening the workbook)?

try

Private Sub ClearALL_Click()
Dim ce As Range
With Worksheets("New CCM Reporting Clinic-Centre").Range("F2:J40")
 .Value = .Range("F2:J40").Value
For Each ce In .Range("F2:J40")
If ce.Value <> 0 Then ce.ClearContents
Next ce
End With
End Sub

I downloaded your workbook, and noticed that the only values in F2:J40 were zeroes. (0, 0%, etc.). This macro won't remove any of those since it's explicitly checking for only cells that are not equal to 0 and clearing those. If you want it to clear cells that *are* 0, change "<>0" to "=0".

I entered a few non-zero values into that range, clicked the button and it worked just fine - leaving blank cells wherever those values were entered.

If you simply want to clear the formulas/contents of all cells in column F (from F2 downward), just use

Dim lastRowF as Long
lastRowF = Range("F" & Rows.Count).End(xlUp).Row
Range("F2:F" & lastRowF).ClearContents