Excel VBA to Replace Blank Cells with Text (3 Examples)

We have a dataset of some students containing their names, class, and percentage in the examination. There are some blank cells in the Percentage column. We will replace those blank cells with text.

Example 1 – A Simple VBA to Replace Blank Cells with Text

This VBA will check the value of each cell one by one. When it finds any blank, it will fill that cell with an input text.

Steps:

• Right-click on the sheet name.
• Choose the View Code option from the Context Menu.

• Enter the VBA window.
• Choose the Module option from the Insert tab.

• The VBA command module appears. We will write the following VBA code here.

• Select all cells of the Percentage column.
• Put the following VBA code on the command module.

Sub Replace_Blank_With_Text_2()
Dim Range1 As Range
Dim Value_1 As String
On Error Resume Next
Value_1 = InputBox("Replace with", "Replace Blank Cell")
For Each Range1 In Selection
If Range1.Text = "" Then Range1.Value = Value_1
    Next Range1
End Sub

• Run the code by pressing the F5 button.
• Write the desired text in the input box.

• Press the OK button.

Look all blank cells are filled with Absent.

Dim Range1 As Range
Dim Value_1 As String

On Error Resume Next

Value_1 = InputBox("Replace with", "Replace Blank Cell")

For Each Range1 In Selection

If Range1.Text = "" Then Range1.Value = Value_1
    Next Range1

Read More: Find and Replace a Text in a Range with Excel VBA

Example 2 – Use the VBA IsEmpty Function

Steps:

• Choose the cells of the Percentage column.
• Copy and paste the following VBA code into the VBA command module.

Sub Replace_Blank_With_Text()
Dim Range1 As Range
Dim Value_1 As String
On Error Resume Next
Value_1 = InputBox("Replace with", "Replace Blank Cell")
For Each Range1 In Selection
If IsEmpty(Range1) Then
Range1.Value = Value_1
End If
Next
End Sub

• Press the F5 button to run the code.
• A window will appear to put the replacement text. Put your text here.

• Press OK.

All blank cells are filled with text.

Dim Range1 As Range
Dim Value_1 As String

On Error Resume Next

Value_1 = InputBox("Replace with", "Replace Blank Cell")

For Each Range1 In Selection

If IsEmpty(Range1) Then
Range1.Value = Value_1

Example 3 – Use vbNullString to Replace Blank Cells

Steps:

• Go to the VBA command module and paste the following VBA code.

Sub Replace_Blank_With_Text_3()
Dim Range1 As Range
For Each Range1 In ActiveSheet.Range("D4:D13")
If Range1 = vbNullString Then Range1 = "Absent"
Next
End Sub

• Press the F5 button to run the VBA code.

Empty cells are filled with text.

Dim Range1 As Range

For Each Range1 In ActiveSheet.Range("D4:D13")

If Range1 = vbNullString Then Range1 = "Absent"

Download the Practice Workbook

Related Articles

• How to Find and Replace Using VBA
• How to Find and Replace from List with Macro in Excel
• Excel VBA: How to Find and Replace Text in Word Document
• Excel VBA to Find and Replace Text in a Column
• How to Find and Replace Multiple Values in Excel with VBA
• Excel VBA: Replace Character in String by Position