Fill Blank Cells With Text Value

[Guest]

I have a large spreadsheet that I would like to fill all blank or rather
empty cells with a text value of "BLANK" Can this be done with an If
statement or Macro? If so, what would it be?

Jason

 

[Dave Peterson]

Record a macro
Select the range to fix (include the non-blank cells, too)
edit|goto special|blanks
type BLANK
hit ctrl-enter to fill all the selected cells (just the empty cells in the
original range)
Stop recording the macro.

Personally, I'd do something like this manually until it was part of a larger
mechanized routine.

 

[Rick Rothstein \(MVP - VB\)]

I have a large spreadsheet that I would like to fill all blank or rather

empty cells with a text value of "BLANK" Can this be done with an If
statement or Macro? If so, what would it be?

You could do it manually this way. Go to the cell that is in the last row
and last column of the range you want to do this over and enter BLANK in
that cell. Now select Edit/Replace from Excel's menu bar (or just key in
Ctrl+H), leave the "Find what" field empty, put BLANK in the "Replace with"
field and, finally, click the Replace All button.

Rick

 

[Guest]

Dave, you will have to dumb this down for me a little bit. I am not use to
macros and I am running Excel 2007

 

[Dave Peterson]

I don't use xl2007 enough to know where those menu items are hidden.

Instead of using edit|goto, use F5 (or ctrl-g).

But the rest should be the same.

 

[Guest]

Don't criticize me for this extremely simple solution but everyone should
know this but I'll gonna tell ya just for thrill of it ... select a blank
cell, type blank in it, now choose format wizard e.g. the magic paint brush
icon and click a cell and drag til your hearts content and click on the magic
paint brush icon and there you go, all the cells you have selected should
have the word 'blank' filled in. It's the simplist way of doing it, you don't
need any defty IT skills and even your good-old grandma can do it.

Cheers,

 

[Dave Peterson]

Using the format painter icon copied that "Blank" value to other cells for
you????

Don't criticize me for this extremely simple solution but everyone should
know this but I'll gonna tell ya just for thrill of it ... select a blank
cell, type blank in it, now choose format wizard e.g. the magic paint brush
icon and click a cell and drag til your hearts content and click on the magic
paint brush icon and there you go, all the cells you have selected should
have the word 'blank' filled in. It's the simplist way of doing it, you don't
need any defty IT skills and even your good-old grandma can do it.

Cheers,