replace null cells within a range to 0

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Abbey Normal - 28 Aug 2007 22:54 GMT

Hi. Is there a way to check a range of cells within a worksheet to see if
they are null, and if so to replace it with a zero? I tried reformatting the
column, but it doesnt do anything with the nulls. It needs to be a number
because I am importing it to a linked file.
Thanks,

Reply to this Message

rdwj - 28 Aug 2007 22:58 GMT

not sure if I understand what you mean with Null, but if you mean Null as in
Empty, you can write an easy macro
Sub Change_Empty_to_Zero
For every mycell in selection
if mycell = "" then mycell = 0
next
exit sub

> Hi. Is there a way to check a range of cells within a worksheet to see if
> they are null, and if so to replace it with a zero? I tried reformatting the
> column, but it doesnt do anything with the nulls. It needs to be a number
> because I am importing it to a linked file.
> Thanks,

Reply to this Message

Jim Thomlinson - 28 Aug 2007 23:00 GMT

Place a Zero in any cell and copy it. Now highlight the area with the
potential null (blank) cells. Select F5 (or ctrl+G or Edit -> Goto) and press
Special... -> Blanks.
All of the blank cells should now be selected. Paste the 0 that you copied.

Signature

HTH...

Jim Thomlinson

Reply to this Message

Dave Peterson - 28 Aug 2007 23:13 GMT

Or just
Select the range
edit|goto|special|blanks
type 0
and hit ctrl-enter to fill all those empty cells in the original selection.

> Place a Zero in any cell and copy it. Now highlight the area with the
> potential null (blank) cells. Select F5 (or ctrl+G or Edit -> Goto) and press
[quoted text clipped - 10 lines]
> > because I am importing it to a linked file.
> > Thanks,

Signature

Dave Peterson

Reply to this Message

Jim Thomlinson - 28 Aug 2007 23:26 GMT

Thanks Dave... I know that I can do that but somehow I just always do the
copy and paste thing. I'm a creature of habit I guess... and somehow I assume
everyone should join me in my habits... :-)

Signature

HTH...

Jim Thomlinson

> Or just
> Select the range
[quoted text clipped - 16 lines]
> > > because I am importing it to a linked file.
> > > Thanks,

Reply to this Message

Abbey Normal - 29 Aug 2007 01:54 GMT

Wow. that worked like a charm! Thank you.

> Thanks Dave... I know that I can do that but somehow I just always do the
> copy and paste thing. I'm a creature of habit I guess... and somehow I assume
[quoted text clipped - 20 lines]
> > > > because I am importing it to a linked file.
> > > > Thanks,

Reply to this Message