Expert: Miguel Zapico - 8/18/2008

Question
Hello all,
I'de like to get zeroes instead of blank spaces in a table of roughly 6,000 codes. The issue is that I run a query weekly that pastes values into 12 columns (for the months of the year) of this table. I would like to get zeroes for the months that do not have any data since that way I could get a pivot tabel to automatically pick up this data.
Thanks

Answer
You could do this manually in a couple of steps, by selecting the whole area, going to menu Edit->Goto->Special->Blanks, typing a 0 and entering it with Crtl+Enter.

You could use a macro like this, that does the same thing.
Sub FillBlanks()
  Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "0"
End Sub

You may need to change the first part if your range doesn't start on A1, or if the current region doesn't cover the full range that you want to fill.

Hope this helps,
Miguel