CODEWITHSUNDEEP
vbaexcel
aniusni
aniusni

Reputation: 59

VBA - filling empty cell with 0

How to fill empty cells with 0 in one column?
I suppose my code is complete but it's not working.

Sub test()

Dim cell As Range

For Each cell In Range("B2307:B10000")
If Len(cell.Value) = 0 Then
    cell.Value = 0
End If
Next

End Sub

Upvotes: 0

Views: 808

Answers (5)

Dominique
Dominique

Reputation: 17493

Without VBA:

Goto Special, Blanks
type '0'
Ctrl+Enter

With VBA:

Selection.SpecialCells(xlCellTypeBlanks).Value = "0"

Upvotes: 2

HiFile.app - best file manager
HiFile.app - best file manager

Reputation: 7954

Despite the fact that the solution by Rosetta is the most elegant one for this very special case (empty cells), I think the following is the most general because you can easily adjust it to more situations by changing the formula.

set rng = Range("B2307:B10000")
rng.Formula = Application.Evaluate("=IF(ISBLANK(" & rng.Address & "),0," & rng.Address & ")")

Remember this pattern, this is so useful!

Upvotes: 0

Rosetta
Rosetta

Reputation: 2725

if you really need vba for this, here is a better way

Sub test()

    On Error Resume Next
    Range("B2307:B10000").SpecialCells(xlCellTypeBlanks) = 0

End Sub

Upvotes: 6

Jonas_Hess
Jonas_Hess

Reputation: 2008

Sub test()
    Dim cell As Range
    For Each cell In Range("B2307:B10000").Cells
        If Len(cell.Value) = 0 Then
            cell.Value = "0"
        End If
    Next
End Sub

Upvotes: 0

Stupid_Intern
Stupid_Intern

Reputation: 3450

Try this:

Sub test()

Dim cell As Range

For Each cell In Range("B2307:B10000")
If Trim(cell.Value) = "" Then
    cell.Value = 0
End If
Next cell

End Sub

Upvotes: 2

Related Questions