Fill Blank Cell with specific values More than 100,000 Rows-CodePudding

I am using an common way that is Selected the range and opens Find and Replace windows then mention specific value in that then replace it with empty cells, this process takes long time.

Then i tried with VBA but it is time taking process as well. I hope there must be any fast way to achieving this. Any help will be appreciated.

Sub fillblankcells()

Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Done")

For Each rng In ws.Range("B2:AB120000")
If IsEmpty(rng) Then
rng.Value = 0
End If
Next

End Sub

CodePudding user response：

Try something like the following. You need to ensure AB120000 is filled before calling SpecialCells.

If IsEmpty(ws.Range("AB120000")) Then
    ws.Range("AB120000").Value = 0
End If

On Error Resume Next ' ignore error if no blank cells
ws.Range("B2:AB120000").SpecialCells(xlCellTypeBlanks).Value = 0
On Error GoTo 0

CodePudding user response：

Just to give you an idea:

I created an Excel sheet, where the cells B2, B120000, AB2 and AB120000 and some random others (inside that area) were filled.
In the "Name box", I entered the range B2:AB120000.
I pressed Ctrl G (Goto), asked for Special, and chose Blanks, that took about a minute.
In the address bar, I typed 0 and pressed Ctrl ENTER.

In less than 2 minutes, all blanks were replaced by zero.

The first comment and answer your received are an automation of this action.

Edit
In case you are interested in the Not Applicable value, you can enter =NA() instead of 0 in the address bar.

Where to put the range? You can see this screenshot: