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.


Download Practice Workbook

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


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.

VBA to Replace Blank Cells with Text

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

VBA to Replace Blank Cells with Text

  • 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

VBA to Replace Blank Cells with Text

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

VBA to Replace Blank Cells with Text

  • 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.\

Read More: How to Replace Text with Blank Cell in Excel (5 Simple Methods)


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

Use VBA IsEmpty Function to replace blank cells

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

  • Finally, press OK.

Use VBA IsEmpty Function

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.

Read More: Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)


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

Use vbNullString to Replace Blank Cells

  • 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.

Read More: How to Replace Text in Selected Cells in Excel (4 Simple Methods)


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.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo