filling blank cells with value 0

[Todd Huttenstine]

Hey guys

I have about 300 total cells in a range. Some of these
cells are blank and others have values in them. Is there
a trick or code that I can use to select a range of cells
or specify a range of cells and put a 0 in all the cells
that are blank?

Thank you

Todd Huttenstine

 

[Ken Wright]

Hi Todd - Select the entire range, hit F5, choose Special / Blank cells/ Type 0
then hit CTRL+ENTER

 

[Rob van Gelder]

Sub test()
On Error Resume Next
With ActiveSheet.Cells(1, 1)
.Resize(.Cells(Rows.Count, 1).End(xlUp).Row, 1) _
.SpecialCells(xlCellTypeBlanks).Value = 0
End With
End Sub

 

[Todd Huttenstine]

Thanks. Very useful tip.

-----Original Message-----
Hi Todd - Select the entire range, hit F5, choose Special / Blank cells/ Type 0
then hit CTRL+ENTER

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

---------------------------------------------------------- ------------------
It's easier to beg forgiveness than ask permission
---------------------------------------------------------- ------------------






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.634 / Virus Database: 406 - Release Date: 18/03/2004


.

 

[Ken Wright]

Lot of good stuff hidden away in that special button. Can use the Special /
visible cells only to select just the visible range of a filtered list and then
copy and paste elsewhere.

 

[Gord Dibben]

Ken

My XL2002 copies just the visible filtered rows without using
F5>Special>Visible cells only.

Yours is different?

Gord

 

[Ken Wright]

Oops - Should have said Subtotals, not Autofilter - my mistake. Thanks for the
catch Gord