Excel Error 1004 - Run-time error '1004' Application-defined or object-defined error

Question

I'm trying to clean up my spreedsheet. There are many blank cells which are throwing off the count. I tried using this code but it's giving me a run-time error.

Sub ClearAll()
    Dim c As Range, MyRange As Range

    Set MyRange = Range("A1:JS87")

    For Each c In MyRange
        If Len(c) = 0 Then c.ClearContents
    Next c

End Sub

Try For each c in MyRange.Cells?

David Zemens
– David Zemens

2013-06-24 19:54:54 +00:00
Commented Jun 24, 2013 at 19:54 — David Zemens
– David Zemens, Commented Jun 24, 2013 at 19:54

score 3 · Accepted Answer · 2013-06-24 20:08:34Z

3

This error might be triggered because of finding a merged cell (or equivalent). I did some tests and IsEmpty seems to skip these situations. In any case, I am also including an error catching to be completely sure.

Sub ClearAll()
    Dim c As Range, MyRange As Range
    Set MyRange = Range("A1:JS87")

    For Each c In MyRange
        On Error Resume Next
        If (Not IsEmpty(c.Value)) Then
            If Len(c) = 0 Then c.ClearContents
        End If
    Next c
End Sub

edited Jun 24, 2013 at 20:08

answered Jun 24, 2013 at 20:00

user2480047

Sign up to request clarification or add additional context in comments.

3 Comments

David Zemens Over a year ago

+1. Merged cells are difficult to work with, and cause problems especially when iterating over cells in a range, like this.

Batman Over a year ago

Is there any way to run this faster? It's taking a really long time to process.

user2480047 Over a year ago

This is a really fast configuration. The only reason I can come up with to explain it being too slow is that the cath part (On Error Resume Next) is actually being used. Error catching is pretty time consuming. But I don't known what might be provoking the errors (comment the aforementioned line and see in which cell it crashes and see what is wrong with this cell). ClearContents shouldn't take too long but I guess that c.Value = "", would be still quicker.

xQbert · Accepted Answer · 2013-06-24 19:59:55Z

2

Perhaps this: changed c to cells instead of range and used cells collection on the range.

Realized you may need c.value as well. Otherwise what property of the cell are you looking at?

Sub ClearAll()
    Dim c As cell, MyRange As Range

    Set MyRange = Range("A1:JS87")

    For Each c In MyRange.cells
        If Len(c.value) = 0 Then c.ClearContents
    Next c

End Sub

MSFT Link

edited Jun 24, 2013 at 19:59

answered Jun 24, 2013 at 19:54

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

1 Comment

David Zemens Over a year ago

.Value is the default property of a cell/range. c = c.Value will return true.

Collectives™ on Stack Overflow

Excel Error 1004 - Run-time error '1004' Application-defined or object-defined error

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related