Prompt to replace empty cells with user-defined text

Question

I need VBA code to check for blank cells within a range. If there are any blanks within that range, a box should come up to allow you to type in what you want to replace the blanks with. The code below does what I want, but the prompt ALWAYS appears, even if there aren't any blanks. How do I make it so the box only appears if there are blanks?

Sub ReplaceBlanks()
  Dim Lastrow As Integer
  Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

  Range("D84:D" & Lastrow).Select
  Dim cell As Range
  Dim InputValue As String
  On Error Resume Next
  InputValue = InputBox("Enter value that will fill empty cells in selection", "Fill Empty Cells")
  For Each cell In Selection
    If IsEmpty(cell) Then
      cell.Value = InputValue
    End If
  Next
End Sub

Properly indenting your code will help make your code easier to read/follow - for you and anyone else looking at it. Anything between `For` and `Next` needs a TAB; anything between `If` and `End If` needs a TAB; anything between `Sub` and `End Sub` needs a TAB. — Mathieu Guindon, Oct 11 '16 at 15:32
Do you want **one** value to fill all cells? Or, for each empty cell, could it be a different `InputValue`? — BruceWayne, Oct 11 '16 at 15:42

score 3 · Accepted Answer · edited May 23 '17 at 12:16

Sub ReplaceBlanks()
Dim Lastrow As Integer
Dim srchRng As Range

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Set srchRng = Range(Cells(84, 4), Cells(Lastrow, 4))

Dim InputValue As String

If srchRng.Count - WorksheetFunction.CountA(srchRng) > 0 Then
    InputValue = InputBox("Enter value that will fill empty cells in selection", _
                          "Fill Empty Cells")
    srchRng.SpecialCells(xlCellTypeBlanks).Value = InputValue
End If
End Sub

This also adds in the range variable, so you avoid using .Select. It also assumes that you only want ONE inputvalue. If you want it to trigger for each empty cell, put the inputValue = ... in the If IsEmpty(cell) loop.

An alternative to your If a cell is empty loop, is a one line fix:

Range(Cells(84,4),Cells(lastRow,4)).SpecialCells(xlCellTypeBlanks).Value = InputValue. That will take ALL blanks in D84:DlastRow and fill in with whatever the InputValue is. No need to loop.

This works but if no blanks it will throw and error (which I see you error handled). Using total count minus countA will never error even with no blanks so I took out the error handling (I like to avoid error handling as much as possible). — Chrismas007, Oct 11 '16 at 15:36
@Robby - I updated the code, removing the Error handler, and the extra (now unused) `Cell` variable. — BruceWayne, Oct 11 '16 at 15:56

score 1 · Answer 2 · answered Oct 11 '16 at 15:29

1

Sub ReplaceBlanks()
Dim Lastrow As Integer
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range("D84:D" & Lastrow).Select
Dim cell As Range
Dim InputValue As String
On Error Resume Next
For Each cell In Selection
If IsEmpty(cell) Then
InputValue = InputBox("Enter value that will fill empty cells in selection", _
"Fill Empty Cells")
cell.Value = InputValue
End If
Next
End Sub

just move the line to the right place :D

answered Oct 11 '16 at 15:29

Pierre

1,046
7
21

But then he has to go a box at a time. I think he wants to fill all empties with one entry. – Chrismas007 Oct 11 '16 at 15:29
if so , it is easy : add "if InputValue = "" then " before InputValue = InputBox("Enter value that will fill empty cells in selection", _ "Fill Empty Cells") – Pierre Oct 11 '16 at 15:39

score 1 · Answer 3 · answered Oct 11 '16 at 15:35

YourRange.Cells.Count - WorksheetFunction.CountA(YourRange) will give you the count of blanks so you can check if you have blanks:

Sub ReplaceBlanks()
    Dim Lastrow As Integer
    Lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row 'Use 4 as it is the D column you are working with

    Dim cel As Range 'Use cel as CELL can be confused with functions
    Dim InputValue As String
    If Range("D84:D" & Lastrow).Cells.Count - WorksheetFunction.CountA(Range("D84:D" & Lastrow)) > 0 Then
        InputValue = InputBox("Enter value that will fill empty cells in selection", "Fill Empty Cells")
        For Each cel In Range("D84:D" & Lastrow)
            If IsEmpty(cel) Then
                cel.Value = InputValue
            End If
        Next
    End If
End Sub

Prompt to replace empty cells with user-defined text

3 Answers3