Allowing ClearContents macro to run with error

[Darren_workforce]

Hello,

I have a large amount of code but the only part I am having issues with is below. Each day's data varies on length so if we have 15 agents versus 30, the number of rows will change. But there will never be 99 agents so I just used that to cover any particular day. On the last line, though, there is always going to be an average line that averages out every column from D:Q. When the cells are being cleared, if all values for a certain metric are zero, the average cell populates a #DIV/0 error. This seems to be what's causing the code to get hung up.

When I go into debug and then delete the cells with the DIV/0 error and then Step Out to run the rest of the macro, it works. So it has to be the error, right?

Is there a way to add a line so that the ClearContents request ignores the errors?

Dim cell, rng As Range
Set rng = Range("D2:Q99")

For Each cell In rng
    If cell.Value = "0" Then
    cell.ClearContents
    Else
    End If
Next cell

 

[JackDanIce]

Try:

Dim cell as range
Dim x as Long

x = Range("Q" and Rows.Count).End(xlUp).Row

For each cell in Range("D2:Q" & x)
     If cell.Value = 0 Then cell.ClearContents
Next cell

If that doesn't work, you could try wrapping an On Error Resume Next outside of the loop, but it may cause other issues for valid errors, however:

Dim cell as range
Dim x as Long

x = Range("Q" and Rows.Count).End(xlUp).Row

On Error Resume Next
For each cell in Range("D2:Q" & x)
     If cell.Value = 0 Then cell.ClearContents
Next cell
On Error Goto 0

Also, are the cells in D2:Q99 filled with formulae? If so, your code is testing for the string "0" rather than the number 0 - reason I've omitted the speech marks from my suggestion

 

[Darren_workforce]

It still generates the Debug popup and the following line is highlighted (both of your suggestions):

x = Range("Q" And Rows.Count).End(xlUp).Row

 

[JackDanIce]

Haven't done VBA in a while, try

x = Range("Q" & Rows.Count).End(xlUp).Row

Or

x = cells(rows.count, "Q").End(xlup).Row

 

[Darren_workforce]

OK I tried the first suggestion and that did the trick!! Thank you @JackDanIce !!!

 

[JackDanIce]

You're very welcome @Darren_workforce thanks for the confirmation and I hope you have a great weekend