CODEWITHSUNDEEP
vbaexcel
Kyle L
Kyle L

Reputation: 35

Replace cells containing zero with blank

I have a very large amount of data A4:EW8000+ that I want to replace cells containing a zero with a blank cell. Formatting the cells is not an option as I need to retain the current format. I'm looking for the fastest way to replace zeros with blank cells.

I can do this with looping but its very slow. Below code:

Sub clearzero() 
Dim rng As Range 
For Each rng In Range("A1:EW10000")
    If rng.Value = 0 Then 
        rng.Value = "" 
    End If 
Next 
End Sub

Is there an easy way I can do this without looping?

I tried the below code, but it doesn't seem to work correctly. It hangs Excel for a while (not responding) then it loops through the range and blanks every cell.

Sub RemoveZero()
  Dim LastRow As Long
  Const StartRow As Long = 2
  LastRow = Cells.Find(What:="0", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  With Range("B:EW")
     .Value = Range("B:EW").Value
     .Replace "0", "0", xlWhole, , False
     On Error Resume Next
     .SpecialCells(xlConstants).Value = ""
     .SpecialCells(xlFormulas).Value = 0
  End With
End Sub

Upvotes: 1

Views: 16833

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

This is all the VBA you need to automate the replacements:

[a4:ew10000].Replace 0, "", 1

.

UPDATE

While the above is concise, the following is likely the fastest way possible. It takes less than a quarter of a second on my computer for your entire range:

Sub RemoveZero()

    Dim i&, j&, v, r As Range

    Set r = [a4:ew10000]
    v = r.Value2
    For i = 1 To UBound(v, 1)
        For j = 1 To UBound(v, 2)
            If Len(v(i, j)) Then
                If v(i, j) = 0 Then r(i, j) = vbNullString
            End If
        Next
    Next

End Sub

Upvotes: 5

user4039065
user4039065

Reputation:

I have found that sometimes it is actually more expedient to cycle through the columns on bulk replace operations like this.

dim c as long
with worksheets("Sheet1")
    with .cells(1, 1).currentregion
        for c = 1 to .columns.count
            with .columns(c)
                .replace what:=0, replacement:=vbNullString, lookat:=xlWhole
            end with
        next c
    end with
end with

Splitting the overall scope into several smaller operations can improve overall performance. My own experience with this is on somewhat larger data blocks (e.g. 142 columns × ~250K rows) and replacing NULL from an SQL feed not zeroes but this should help.

Upvotes: 3

Related Questions