Fill Blanks From Below

[blkmagik]

Hello
I understand that the manual method for filling in blank cells from below
would be to:

• Select the first blank cell
• type = and hit the down arrow to creates the reference.
• copy the cell, highlight the column
• Edit - Go to - Special - Blanks and paste

I was curious if there is a macro that will allow me to do this all in one shot? I tried recording it but, it doesn't seem to record the process.

Ive found a macro but it doesn't loop and requires me to run it over and over again until it debugs and say no cells found.

Sub Fill_Blanks_From_Below()
Dim rng As Range, ar As Range
Set rng = Columns(1).SpecialCells(xlBlanks)
For Each ar In rng.Areas
ar.Value = ar.Offset(1, 0).Value
Next ar
End Sub

Thanks in advance.

 

[Rick Rothstein]

This macro will do what you have asked for...

Sub FillBlanksFromBelow()
  Dim A As Range
  On Error GoTo NoBlanks
  For Each A In Columns("A").SpecialCells(xlCellTypeBlanks).Areas
    A.FormulaR1C1 = "=R[1]C"
    A.Value = A.Value
  Next
NoBlanks:
End Sub

 

[blkmagik]

Thank you so very much, appreciate your help and expertise

 

[Rick Rothstein]

Thank you so very much, appreciate your help and expertise

You are quite welcome. By the way, I have been thinking about your problem some more and came up with a slightly shorter, probably faster solution for you. Since we are talking about constant data in Column A, this code should also do what you want (notice... no loops).

Sub FillBlanksFromBelow()
  On Error GoTo NoBlanks
  With Range("A1").Resize(Cells(Rows.Count, "A").End(xlUp).Row)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"
    .Cells.Value = .Cells.Value
  End With
NoBlanks:
End Sub