Fill blank cells with a specific value
How to fill blank cells with a specific value using an Excel and VBA method
Select the range which has blank cells > Select the Home tab > Editing group > Click Find & Select > Click Go to Special > Select Blanks > Click OK > Enter value to fill > Press Ctrl + Enter keys simultaneously
| In this example we will be populating the blank cell in range (B3:D9) with 0. This image shows the starting point without any adjustments. |  |
| 1. Select the range in which you want to fill the blank cells with a value. Note: in this example we are selecting range (B3:D9) which captures four blank cells. |
 |
| 2. Select the Home tab. |  |
| 3. Click Find & Select in the Editing group. 4. Click Go To Special. |
 |
| 5. Select Blanks in the Go To Special window. 6. Click OK |
 |
| 7. Enter the value that you want to populate all of the blank cells into the first selected cell. Note: in this example we are filling all the blank cells with 0, therefore, we have entered 0 into the first selected cell. |
 |
| 8. Press Ctrl + Enter keys simultaneously to copy the value to the remaining selected blank cells. |  |
Sub Fill_blank_cells_with_a_specific_value()
'declare a variable
Dim ws As Worksheet
Dim rng As Range
Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Analysis")
'fill blank cells with 0
For Each rng In ws.Range("B3:D9")
If IsEmpty(rng) Then
rng.Value = 0
For Each rng In ws.Range("B3:D9")
If IsEmpty(rng) Then
rng.Value = 0
End If
Next
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analyst.
Values Range: If using the exact same VBA code make sure that the values that you want to test for blank cells and fill with a specific value are captured in range ("B3:D9").
Worksheet Name: Have a worksheet named Analyst.
Values Range: If using the exact same VBA code make sure that the values that you want to test for blank cells and fill with a specific value are captured in range ("B3:D9").
ADJUSTABLE PARAMETERS
Value Range: Select the values that you want to test for blank cells and fill with a specific value by changing the range ("B3:D9") to any range in the Analysis worksheet that doesn't conflict with the formula.
EXPLANATION
This tutorial explains and provides step by step instructions on how to fill blank cells with a specific value using an Excel and VBA method.
This tutorial explains and provides step by step instructions on how to fill blank cells with a specific value using an Excel and VBA method.
Excel Method: Using an Excel method you can paste a specific value to blank cells through the use of the Go To Special command button.
VBA Method: Using a VBA method you can automatically identify blank cells in a specified range and fill the with a specific value.