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.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Slashman" wrote:
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.

Try this ...

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


*** Please do rate ***





"Slashman" wrote:

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.

"Slashman" wrote in message
ups.com...
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

Joerg

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

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


Gord Dibben MS Excel MVP

On Fri, 25 Aug 2006 18:13:41 +0900, "Joerg" wrote:

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

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



Gord Dibben wrote:
Joerg

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

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


Gord Dibben MS Excel MVP

On Fri, 25 Aug 2006 18:13:41 +0900, "Joerg" wrote:

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

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

Cheers,

Aaron.
shail wrote:
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



Gord Dibben wrote:
Joerg

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

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


Gord Dibben MS Excel MVP

On Fri, 25 Aug 2006 18:13:41 +0900, "Joerg" wrote:

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

A short note to say thanks for calling back, brother !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Slashman" wrote in message
oups.com...
Thanks heaps everyone, those solutions did exactly what I needed.

Cheers,

Aaron.