Filling empty cells

**bukimiak** · 03-17-2011, 07:50 AM

Hello!

I had to add a little code to another macro to make it fill empty cells with a specified number (973).

I found almost ready code and modified it to fit my needs.

Currently it looks like that:

Sub puste()

  Dim Area As Range, LastRow As Long
  On Error Resume Next
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
               SearchDirection:=xlPrevious, _
               LookIn:=xlFormulas).Row
  For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
               SpecialCells(xlCellTypeBlanks).Areas
    Area.Value = "973"
  Next
End Sub

I have 2 questions:
1. Is there an easier way to do it? Maybe a lot faster one.
2. What do I have to change to make it work on column A instead of current selection?

Thanks for all the tips!

**watersev** · 03-17-2011, 08:03 AM

hi, bukimiak, this will place 973 in empty cells in the used range of column A:

Range([a1], Cells(Rows.Count, "a").End(xlUp)).Replace What:="", Replacement:=973

**bukimiak** · 03-17-2011, 08:14 AM

Wow, as simple as that.

I think I tried with "replacement" but I gave "" instead of " " and it did not work.
(I forgot about the space in the middle).

Thank you for your help!