Fill cell that is blank

S

Slashman

Hi,

I have a column that is 700 odd rows long. In each cell there is a
phone number, but in some cells in the column there is no data at all.

How can I run a macro to look at every cell in the column and if it is
blank, add a number, word or something and if it is not blank, to leave
it and continue to the next?

Cheers,

Aaron.
 
M

Max

Try this on a spare copy ..

Assuming source data in A1 down

Put in B1:
=IF(A1="","xxx",A1)
Copy B1 down to last row of data in col A
This will fill blank cells or cells with "" (zero length strings) in col A
with "xxx"

Then freeze the evaluated values in col B with an in-place: Copy > Paste
special > Check "Values" > OK. Then delete col A.
 
N

Naveen

Try this ...

Sub Naveen()
Cells.Replace What:="", Replacement:="Slashman value", LookAt:=xlWhole
End Sub


*** Please do rate ***
 
J

Joerg

Slashman said:
Hi,

I have a column that is 700 odd rows long. In each cell there is a
phone number, but in some cells in the column there is no data at all.

How can I run a macro to look at every cell in the column and if it is
blank, add a number, word or something and if it is not blank, to leave
it and continue to the next?

Cheers,

Aaron.
Click to expand...


Why do you need a macro?
1) Select your column up to the last entry
2) Push F5 -. Special -> Blanks (this will select all blank cells in your
previous selection)
3) Add your number, word or whatever
4) Push ENTER to go to the next blank cell

Cheers,
Joerg
 
G

Gord Dibben

Joerg

Little easier method.............

At step #4 hit CTRL + ENTER to add the word to all selected blanks.


Gord Dibben MS Excel MVP
 
S

shail

Another method

1. Select the sheet upto your need
2. Press Ctrl+H
3. Leave the Find what box blank
4. Enter your words at Replace with box
5. Click Replace All button

Bingo....

thanks

Shail
 
Top