CODEWITHSUNDEEP
excelvbasubstitution
DutchArjo
DutchArjo

Reputation: 359

Replace empty by 0

I want to check all cells in columns D to O. If the cell is empty, replace it by a hard zero.

I have this code:

Sub replace()
Dim rng As Range, cell As Range
Dim aantalrijen As Long

    With Worksheets("Schaduwblad")
        aantalrijen = .Range("A1", .Range("A1").End(xlDown)).Cells.Count - 1
        Set rng = .Range(.Cells(2, "D"), .Cells(aantalrijen, "O"))

        For Each cell In rng
            cell = WorksheetFunction.Substitute(cell, "", "0")
        Next
    End With
End Sub

This code hangs during processing. Only option is to end the routine by pressing Escape.

Upvotes: 3

Views: 5767

Answers (2)

CLR
CLR

Reputation: 12289

You don't need to loop through all the cells. Let Excel find the empties with .SpecialCells:

On Error Resume Next
rng.SpecialCells(xlCellTypeBlanks, xlCellTypeConstants).Value = 0
On Error GoTo 0

The error trap is required in case no empty cells are found.

So your whole routine could be replaced with:

Sub replace()

    On Error Resume Next

      With Worksheets("Schaduwblad")
            .Range(.Cells(2, "D"), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, "O")) _
                .SpecialCells(xlCellTypeBlanks, xlCellTypeConstants).Value = 0
      End With

    On Error GoTo 0

End Sub

Further to your comment below, here is a version of the same code, but working on a row-by-row basis. To test this, I built a 227,000 x 15 block of data and then using a random number generator punched 100,000 holes into it, emptying those cells. I then ran the following code, which took 33 seconds to fill those 100,000 holes back in.

Sub replace()

Dim rangesection As Range

    On Error Resume Next

      With Worksheets("Schaduwblad")
        For Each rangesection In .Range(.Cells(2, "D"), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, "O")).Rows
            rangesection.SpecialCells(xlCellTypeBlanks, xlCellTypeConstants).Value = 0
        Next
      End With

    On Error GoTo 0

End Sub

Upvotes: 6

T. Nesset
T. Nesset

Reputation: 417

I've never used the substitute methode.. I would do this by checking if cell is empty with the IsEmpty() function.

So yo can swap

cell = WorksheetFunction.Substitute(cell, "", "0")

with

If IsEmpty(cell) Then cell.value = 0

Full code:

Sub replace()
Dim rng As Range, cell As Range
Dim aantalrijen As Long

  With Application.ThisWorkbook.Worksheets("Schaduwblad")
        aantalrijen = .Range("A1", .Range("A1").End(xlDown)).Cells.Count - 1
        Set rng = .Range(.Cells(2, "D"), .Cells(aantalrijen, "O"))


    For Each cell In rng
        If IsEmpty(cell) Then cell.value = 0
    Next
  End With
End Sub

Upvotes: 2

Related Questions