Automatically fill 0 and 0% in empty Excel cells

Question

I am trying to automatically enter 0 and 0% values ​​in empty cells in Excel. I have a report in Excel that is automatically populated from SAS . After saving this report, I want the empty cells to be automatically filled as 0 in numeric columns and 0% in percentage of columns.

What will be the macro or VBA code for this?

Answer 1

If you just want to add 0 to an empty cell, there are several ways to do this - here one uses the A1: D10 range as an example. Note that if the cell is formatted as a percentage, then "%" is automatically added to 0 .:

Sub test()

Dim cell As Range

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

End Sub

, ( ), , Application.ScreenUpdating = False Application.ScreenUpdating = True . .

Answer 2

( ), , SpecialCells, , , .

.

• F5... ..
• add 0
• Ctrl + Enter

Sub Quickfull()
'reset usedrange
ActiveSheet.UsedRange
On Error Resume Next
ActiveSheet.Cells.SpecialCells(xlBlanks).Value = 0
End Sub