VBA - Runtime Error 13 on simple macro

[EdwardSurrey]

Hello. I am using the following code to search for any cells that contain " " within a set range, and then clear the contents of those cells. I get a Type Mismatch (Run-time Error 13) and the debug highlights that the
If cell.Value = " " Then part. It does seem to work on cells it look at until it errors. I've been struggling to solve all morning - am I missing something obvious?

Sub ClearDataV2()
Application.ScreenUpdating = False
For Each cell In Range("ImportData2")
If cell.Value = " " Then
cell.ClearContents
End If
Application.ScreenUpdating = True
Next
End Sub

 

[Joe4]

Do any of the cells it is checking have errors in them?

 

[Fluff]

Rather than looping through the cells, you can do it in one hit like (this assumes that the cells do not contain a formula)

Sub EdwardSurrey()
   Range("ImportData2").Replace " ", "", xlWhole, , , , False, False
End Sub

 

[Peter_SSs]

I agree with Joe's question implying most likely error values in the range.

Edit: Too slow typing this part.

But why not do them all at once?

Sub ClearDataV2a()
  Range("ImportData2").Replace What:=" ", Replacement:="", LookAt:=xlWhole
End Sub

 

[EdwardSurrey]

I agree with Joe's question implying most likely error values in the range.

Edit: Too slow typing this part.

But why not do them all at once?

Sub ClearDataV2a()
  Range("ImportData2").Replace What:=" ", Replacement:="", LookAt:=xlWhole
End Sub

Aha, thanks! - But then how can I make it ignore the errors?

 

[Fluff]

The code that Peter & I posted will ignore the errors.

 

[EdwardSurrey]

Rather than looping through the cells, you can do it in one hit like (this assumes that the cells do not contain a formula)

Sub EdwardSurrey()
   Range("ImportData2").Replace " ", "", xlWhole, , , , False, False
End Sub

Amazing - Works in an instant and without any errors at all Thank you!

Thanks also @Peter_SSs for your help.

 

[Fluff]

Glad we could help & thanks for the feedback.