Filling in Zeros

  • Thread starter Thread starter Szuja
  • Start date Start date
S

Szuja

Last call for help, I promise! :)

I used to know how to do this, but... I have two columns, of which
about 2/3 are full of numbers while the rest are blank. I want to fill
these blanks with zeros without typing it by hand (since the columns
are about 25000 cells deep).

The Excel Help hasn't been very helpful. Ideas? Thank you in advance!
:D
 
One way, select the range, press F5, click special, select blanks,
type 0 and press ctrl + enter
 
Check out Edit>Replace in the menubar

For a macro solution see below

First I want you give a answer with specialcells but you are close to the limit of that.

Try this for column A and B of the activesheet

Private Sub Testing()
Dim r As Long
Application.ScreenUpdating = False
With ActiveSheet
For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 1 Step -1
If Trim(.Cells(r, "A").Value) = "" Then .Cells(r, "A").Value = 0
If Trim(.Cells(r, "B").Value) = "" Then .Cells(r, "B").Value = 0
End If
Next
End With
Application.ScreenUpdating = True
End Sub
 
One way:

Select both columns. Choose Edit/Goto/Special, and select the Blanks
radio button. Click OK.

Enter 0 in the formula bar and hit CTRL-SHIFT-ENTER.
 
One way;
Auto filter the column to blanks only. Paint the range
then F5 click special click visible cells only. type 0
then ctrl + enter
good luck
ray
 
Remove the End If in the macro if you use that one

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




Ron de Bruin said:
Check out Edit>Replace in the menubar

For a macro solution see below

First I want you give a answer with specialcells but you are close to the limit of that.

Try this for column A and B of the activesheet

Private Sub Testing()
Dim r As Long
Application.ScreenUpdating = False
With ActiveSheet
For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 1 Step -1
If Trim(.Cells(r, "A").Value) = "" Then .Cells(r, "A").Value = 0
If Trim(.Cells(r, "B").Value) = "" Then .Cells(r, "B").Value = 0
End If
Next
End With
Application.ScreenUpdating = True
End Sub
Click to expand...
 
No need to enter it in the formula bar, just type in the zero when the
blanks are selected,
also ctrl & enter, not ctrl + shift & enter
 
Thanks for the correction, Peo!

I tell people to enter it in the formula bar only because I've found
that many folks select one of the cells, first.

No excuse for CTRL-ENTER, though - I've gotten bitten on that
several times.
 
Note :
If there are more then 8192 non-contiguous ranges in the two columns you
can't use specialcells.

The OP say 1/3 of 25000 rows in two columns(So he is close)
 
Szuja, Must be an easier way, but here's my solution. Create a
column next to the one you want to check and adapt the following
formula, =IF(A1>0,A1,0), then do a paste, special values on the
column. Finally you can delete the original column. HTH
 
Back
Top