Hide all #div/0 until data entered in (Hide all at once)

[dsrt16]

I know how to use the IF formula to remove the #div/0! from cells containing formulas that don't have data yet for them to work. However, I have a few, and I don't want to go through each one and add the IF statement. In previous workbooks, I put the formula in the IF (isblank) right from the start. But I didn't on this one, and the thought of going in and changing them all isn't a happy one right now.

Is there a macro that will do this for all the formulas in the entire workbook? The formulas are all Average() formulas. If not, I can certainly go through and add it in each row with that and drag it across to change them all, but I am just hoping to avoid that.

 

[steve the fish]

Try something like this:

For Each sh In ThisWorkbook.Worksheets
    For Each c In sh.UsedRange
        If c.HasFormula = True Then
            If InStr(LCase(c.Formula), "average") > 0 Then
                If InStr(LCase(c.Formula), "iferror") = 0 Then
                    c.Formula = "=IFERROR(" & Mid(c.Formula, 2, Len(c.Formula)) & ",""ERROR"")"
                End If
            End If
        End If
    Next
Next

 

[steve the fish]

Oh and test it on a copy of your workbook!!!