Help modifying a code for filling blanks in selected range with zeroes

[edge37]

Hi, I am using this code to fill a selected range of cells with zeros if they are blank, the thing is that this code fills the blank cells one by one, and could take some time to finish. Can you help me please modify this code so it fills blank cells from a selected range all at once automatically?

Sub InsertZeroes()
    For Each c In Selection.Cells
    If c.Value = "" Then c.Value = 0
    Next
End Sub

Thank you

 

[Cubist]

Try this:

Sub InsertZeroes()
    Dim data As Variant
    Dim i As Long, j As Long
    Dim t As Double: t = Timer
    
    data = Selection.Value
    For i = LBound(data, 1) To UBound(data, 1)
        For j = LBound(data, 2) To UBound(data, 2)
            If IsEmpty(data(i, j)) Then data(i, j) = 0
        Next j
    Next i
    
    Selection.Value = data
    MsgBox Round(Timer - t, 2) & " seconds" 
End Sub

 

[NoSparks]

Maybe

Sub trythis()
    Selection.SpecialCells(xlBlanks).Value = 0
End Sub

 

[edge37]

Thank you both, wonderful

 

[Cubist]

Maybe

Sub trythis()
    Selection.SpecialCells(xlBlanks).Value = 0
End Sub

I have tried this approach before suggesting the loop however it doesn't seem to work on a completely blank range. There must be at least 1 non-blank value in the range.

 

[Cubist]

Thank you both, wonderful

You're welcome, but try this. I've added an error handler to @edge37 suggestion above it seems to work a lot faster.

Sub InsertZeroesv2()
    On Error GoTo errHandler
    Selection.SpecialCells(xlCellTypeBlanks).Value = 0
    Exit Sub
errHandler:
    Selection.Value = 0
End Sub

 

[NoSparks]

@Cubist, you sure you don't have the posters mixed up ?

I have tried this approach before suggesting the loop however it doesn't seem to work on a completely blank range. There must be at least 1 non-blank value in the range.

My testing was with totally blank ranges and there was no issues.
What loop ?

 

[Cubist]

When I run I get this. I was referring to the For loop in post#2. And yes I got the names swapped.

 

[NoSparks]

@Cubist, you're definately right for what you show.

I suspect those selected cells are not within the sheets usedrange which I believe is what SpecialCells is restricted to.
Expermintation shows me if part of the selection was within the sheets usedrange only they would have been zero'd
and (this one could be dangerous) if only a single cell within the usedrange is selected all blanks within the usedrange are zero'd