Fill empty or blank cells in selection with a prompted value - an example

[DataFreakFromUtah]

No question here. Just an example procedure for the archive.

Search critera: Fill empty or blank cells in selection with a prompted value
Change empty cells to a specific value. Fill blanks with text or numbers.
Insert values into empty cells.


Sub FillEmptyBlankCellWithValue()
'Fill an empty or blank cell in selection
'with value specified in InputBox
Dim cell As Range
Dim InputValue As String
On Error Resume Next

'Prompt for value to fill empty cells in selection
InputValue = InputBox("Enter value that will fill empty cells in selection", _
"Fill Empty Cells")
'Test for empty cell. If empty, fill cell with value given
For Each cell In Selection
If IsEmpty(cell) Then
cell.Value = InputValue
End If
Next
End Sub

 

[JE McGimpsey]

Just a suggestion:

Public Sub FillEmptyBlankCellWithValue()
Const sPROMPT = _
"Enter value that will fill empty cells in selection"
Const sTITLE = "Fill Empty Cells"
Dim InputValue As Variant
Do
InputValue = Application.InputBox( _
Prompt:=sPROMPT, _
Title:=sTITLE, _
Type:=2)
If InputValue = False Then Exit Sub 'User cancelled
Loop Until Len(InputValue) > 0
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Value = InputValue
On Error GoTo 0
End Sub