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

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

Download Practice Workbook

3 VBA Examples to Replace Blank Cells with Text in Excel

1. A Simple VBA to Replace Blank Cells with Text

2. Use VBA IsEmpty Function

3. Use vbNullString to Replace Blank Cells

Conclusion

Download Practice Workbook

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

Replace Blank Cells with Text.xlsm

3 VBA Examples to Replace Blank Cells with Text in Excel

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.

Code Explanation:

Dim Range1 As Range
Dim Value_1 As String

These two lines declare the variables first.

On Error Resume Next

If any error is found, resume the next section.

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

Use InsertBox to input the value of Value_1.

For Each Range1 In Selection

Apply a for statement.

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

If the value of the Range1 is blank, then fill it with the value of Value_1.\

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.

Code Explanation:

Dim Range1 As Range
Dim Value_1 As String

These two macros declare the variables first.

On Error Resume Next

If any error is found, resume the next section.

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

Use the InputBox method to insert a value.

For Each Range1 In Selection

Apply a for statement.

If IsEmpty(Range1) Then
Range1.Value = Value_1

Check if the value of Range1 is empty or not. If empty then fill the value of Range1 by the Value_1.

Similar Readings

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.

Code Explanation:

Dim Range1 As Range

Declare a variable.

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

Apply a for the statement on Range1, whose range is D4:D13.

If Range1 = vbNullString Then Range1 = "Absent"

If the value of Range1 is Null then fill up that by Absent.

Conclusion

In this article, we showed 3 VBA examples to replace blank cells with text in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.