[excel] Excel how to fill all selected blank cells with text

Is it possible to select all the bank cells in an excel sheet and put the same value in all the cells?

I just want to populate them with "null"

I have Excel student 2010

If all the cells are under one column, you could just filter the column and then select "(blank)" and then insert any value into the cells. But be careful, press "alt + 4" to make sure you are inserting value into the visible cells only.

If you want to do this in VBA, then this is a shorter method:

Sub FillBlanksWithNull()

'This macro will fill all "blank" cells with the text "Null"

'When no range is selected, it starts at A1 until the last used row/column

'When a range is selected prior, only the blank cell in the range will be used.

On Error GoTo ErrHandler:

Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "Null"

Exit Sub

ErrHandler:

MsgBox "No blank cells found", vbDefaultButton1, Error

Resume Next

End Sub

Regards,

Robert Ilbrink

Here's a tricky way to do this - select the cells that you want to replace and in Excel 2010 select F5 to bring up the "goto" box. Hit the "special" button. Select "blanks" - this should select all the cells that are blank. Enter NULL or whatever you want in the formula box and hit ctrl + enter to apply to all selected cells. Easy!

I don't believe search and replace will do it for you (doesn't work for me in Excel 2010 Home). Are you sure you want to put "null" in EVERY cell in the sheet? That is millions of cells, in which case there is no way a search and replace would be able to handle it memory-wise (correct me if I am wrong).

In the case I am right and you don't want millions of "null" cells, then here is a macro. It asks you to select the range then put "null" inside every cell that was blank.

Sub FillWithNull()

Dim cell As range
Dim myRange As range

Set myRange = Application.InputBox("Select the range", Type:=8)
Application.ScreenUpdating = False

For Each cell In myRange
    If Len(cell) = 0 Then
        cell.Value = "Null"
    End If
Next

Application.ScreenUpdating = True

End Sub

To put the same text in a certain number of cells do the following:

1. Highlight the cells you want to put text into
2. Type into the cell you're currently in the data you want to be repeated
3. Hold Crtl and press 'return'

You will find that all the highlighted cells now have the same data in them. Have a nice day.