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

We can detect blank cells and replace them with text using different methods in Excel. Excel VBA is one of the methods. In this section, we will show 3 VBA examples to replace blank cells with text in Excel with the proper illustration.

Excel VBA to Replace Blank Cells with Text: 3 Examples

We will show 3 VBA examples to replace the blanks with text. We have taken 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.

1. A Simple VBA to Replace Blank Cells with Text

This VBA will check the value of each cell one by one. And when it finds any blank, then it will fill that cell with the desired text.

Steps:

• First, go to the bottom section of the sheet.
• Press the right button of the mouse.
• Choose the View Code option from the Context Menu.

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

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

• First, select all cells of the Percentage column.
• Then, 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

• Now, run the code by pressing the F5 button.
• Write the desired text on the newly appeared window.

• Finally, 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

2. Use VBA IsEmpty Function

In this example, we will use the IsEmpty function in VBA to replace blank cells with text values. For that, follow the steps below.

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

• Then, press the F5 button to run the code.
• A window will appear to put the replacement text. Put Absent here.

• Finally, press OK.

Now, look at the dataset. 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

3. Use vbNullString to Replace Blank Cells

In this example, we will use the 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.

Have a look at the dataset. 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 Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we showed 3 VBA examples to replace blank cells with text in Excel. I hope this will satisfy your needs and give your suggestions in the comment box.

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