Fill cell that is blank
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. |
Fill cell that is blank
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. |
Fill cell that is blank
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. |
Fill cell that is blank
"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 |
Fill cell that is blank
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 |
Fill cell that is blank
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 |
Fill cell that is blank
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 |
Fill cell that is blank
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. |
All times are GMT +1. The time now is 05:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com