How To Automate Copy & Paste....

[Burrgogi]

In col. A, I have some "stuff"... mostly text.
In Col. B, I have this:
B1: Peaches
B2: --
B3: Oranges
B4: --
B5: --
B6: Nuts & Bolts
B7: Smith & Kline
B8: --
B9: Swimming Pool
B10: --
B11: --
B12: --
B13: --
B14: --


meaning that there is nothing contained in cell A15. So Loop, 'copy & paste' action as long as there is something in col. A.

dashes '--' denote empty cells.

Is there some function or VBA routine that can automatically fill in the empty cells so that for example in B2, it will show the value just above it - which in this case is Peaches. Copy & paste works fine, but the problem is that the number of empty cells (as you can see from the example here) varies. So if you look at the last item Swimming Pool, there are 5 empty rows compared to peaches which has only 1 empty cell. I'm trying to get the end result to look like this:

B1: Peaches
B2: Peaches
B3: Oranges
B4: Oranges
B5: Oranges
B6: Nuts & Bolts
B7: Smith & Kline
B8: Smith & Kline
B9: Swimming Pool
B10: Swimming Pool
B11: Swimming Pool
B12: Swimming Pool
B13: Swimming Pool
B14: Swimming Pool

 

[VoG]

Try

Sub FillBly()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B1:B" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

 

[Burrgogi]

Peter it works great. Thanks for the help!

 

[Burrgogi]

Sorry guys, but I need a little more help on this one. I changed the original worksheet so now that it has a column header in row 1. (Before, I had none). Now when I run the macro provided by VoG, it turns a =#REF! error each of the blank cells. What change(s) do I need to make to the code?

 

[MARK858]

Just change B1 to B2

With Range("B1:B" & LR)

 

[Burrgogi]

Just change B1 to B2

With Range("B1:B" & LR)

Thanks Mark - got it now.

 

[Burrgogi]

For some reason the code provided by VoG keeps breaking. The last couple of times I ran the routine, it didn't work. Instead of the giving me the cell values, I got the following:

B1: Peaches
B2: =R[-1]C
B3: Oranges
B4: =R[-1]C
B5: =R[-1]C
B6: Nuts & Bolts
B7: Smith & Kline
B8: =R[-1]C
B9: Swimming Pool
B10: =R[-1]C
B11: =R[-1]C
B12: =R[-1]C
B13: =R[-1]C
B14: =R[-1]C

 

[VoG]

Try

Sub FillBly()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B2:B" & LR)
    .NumberFormat = "General"
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

 

[Burrgogi]

VoG, I got an error message in VBE saying:

Run time '1004'
"No cells were found"

 

[VoG]

Try selecting column B and run the TrimAll macro http://dmcritchie.mvps.org/excel/join.htm

Then run

Sub FillBly()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B2:B" & LR)
    .NumberFormat = "General"
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    On Error GoTo 0
    .Value = .Value
End With
End Sub