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.
- 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.\
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
- 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.
Read More: Replace Text of a Cell Based on Condition in Excel (5 Easy Methods)
Similar Readings
- Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
- Excel VBA: How to Find and Replace Text in Word Document
- How to Replace Text in Excel Formula (7 Easy Ways)
- Excel VBA: How to Replace Text in String (5 Effective Ways)
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.
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
- Excel Formula to Replace Text with Number (5 Examples)
- Excel VBA: Replace Character in String by Position (4 Effective Ways)
- How to Replace Text after Specific Character in Excel (3 Methods)
- Excel VBA: Open Word Document and Replace Text (6 Examples)
- How to Replace Text between Two Characters in Excel (3 Easy Ways)
- Excel VBA to Find and Replace Text in a Column (2 Examples)