Macro to copy cell value down until next non-blank cell

[nodroj81]

Hi all,

Let me lay out my problem so you can understand what I'm trying to accomplish.

In a given sheet (Sheet1), I have two columns,

A1 and B1 are column headings

column A has names, so let's say A2 = John, A17 = Fred, A47 = Larry, and A91 = Zeus. All cells between the cells mentioned are blank.

Column B has order numbers, and every cell from B2 - B125 has an order number in it.

So my array in question is A1:B125.

What I am trying to do is find a macro that will look in column A and if the cell is blank, copy the name from above. If the cell isn't blank then the cell remains unchanged. This is done until the last row of data is reached. Please note that in the example above the last row of data for column A would be A125 (not A91).

I can actually put together a working macro that would essentially insert a temp column, insert a formula, copy the values of that temp column and then delete the temp column. That just seems like an incredibly inefficient way of doing it.

Any ideas?

 

[Cornstarch]

Hello all,

I got to troubleshoot something in Vidar's suggestion:
I am confused by the "c" which I assume is referring to "Cells" for now my VBA alert me for "Variable not defined error". Any suggestions? Thanks!
Trying to define the variable is not making it too.

Sub FillRow()
Dim Name As String
For Each c In Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
If Cells(c.Row, 1) > "" Then
Name = Cells(c.Row, 1).Value
Cells(c.Row, 1).Copy
Else
Cells(c.Row, 1).Value = Name
Cells(c.Row, 1).PasteSpecial (xlPasteFormats)
End If
Next
End Sub