VBA To Fill Blank Cells

[sswcharlie]

I have two columns with data (length of column B represents total length of database.) Column A only has some blank cells.

I need to fill Column A blanks with the contents of cell above.

Monday
Tuesday
[blank cell]
Wednesday
Thursday
[blank cell]
[blank cell]
***end of column B

Therefore VBA to place Tuesday into blank and Thursday into the two blanks before bottom of the range of Column B.

Using record macro doesnt give me go to first blank etc.

What is the best way. - ?

something like

Start column A2 to End
Find first blank, copy active cell -1
Next blank cell. (ins some cases it will copy the name it has just filled into cell above.
Finish at last cell in Comumn B

Thanks
Charlie

[Reafidy]

Hi,

Try:

Sub HTH() 
    Dim RowNo As Long, LastRow As Long 
    With Worksheets(1) 
        LastRow = .Range("B" & Rows.Count).End(xlUp).Row 
        Do 
            Set c = .Columns(1).Find("", after:=.[A2], LookIn:=xlValues) 
            If c Is Nothing Or c.Row > LastRow Then Exit Do 
            c.Value = c.Offset(-1).Value 
        Loop 
    End With 
End Sub 

[mikerickson]

This approach uses SpecialCells rather than Find.

Sub test() 
    Dim xray As Range 
    With Sheets("sheet1") 
        On Error Resume Next 
        For Each xray In Range(.Range("b1"), .Cells(.Rows.Count, 2).End(xlUp)).Offset(0, -1).SpecialCells(xlCellTypeBlanks) 
            xray.Value = xray.Offset(-1, 0).Value 
        Next xray 
        On Error Goto 0 
    End With 
End Sub 

[jindon]

I have two columns with data (length of column B represents total length of database.) Column A only has some blank cells.

I need to fill Column A blanks with the contents of cell above.

Monday
Tuesday
[blank cell]
Wednesday
Thursday
[blank cell]
[blank cell]
***end of column B

No Loop

Sub test() 
    On Error Resume Next 
    With Range("b1", Range("b" & Rows.Count).End(xlUp)).Offset(,-1).SpecialCells(4) 
        .FormulaR1C1 = "=R[-1]C" 
        .Value = .Value 
    End With 
End Sub 

Edited: code

[Reafidy]

Nice work jindon.

[Dave Hawley]

We have an free add-in zip for this here called Fill Blanks Excel Add-in Fill blanks in a list with the cell above

Code used is shown here with details on code and an esay formula manual method: Excel Formula and VBA Macro Code To Fill All Blank Cells In a List

[sswcharlie]

Excellent results. Thanks everbody.