Fill cell that is blank

[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.

 

[Guest]

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.

 

[Guest]

Try this ...

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


*** Please do rate ***

 

[Joerg]

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.

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

 

[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

 

[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

 

[Slashman]

Thanks heaps everyone, those solutions did exactly what I needed.

Cheers,

Aaron.

 

[Max]

A short note to say thanks for calling back, brother !