Help with a macro please

[Buns1976]

Hi Everyone,

I have a workbook with Sheet 1 in it. Each day Sheet 1 is updated with up to 600 rows of data. We have several rows that we need to copy data to for example, where Cells A3, A4, B3 & B4 below are empty I would like to copy A2 & B2 and so forth down column A & B. I'm trying to come up with a macro to do that if someone could be so kind.

Thank you!!

Column A Column B C D E F

Row1-00000000010054/000	20 OZ FOUNTIAN	$1.19	4	5	$5.95
Row2-00000000010078/000	32 OZ FOUNTIAN	$1.39	21	21	$29.19
Row3-		$1.19	1	1	$1.19
Row4-				22	$30.38
Row5-00000000010511/000	Refill Fntn	$0.79	11	11	$8.69
Row6-00000000010528/000	44 OZ FOUNTIAN	$1.29	3	3	$3.87
Row7-		$1.49	17	21	$31.29
Row8-				24	$35.16
00000000011037/000	16oz Coffee	$1.39	4	4	$5.56

<tbody>
</tbody>

 

[Michael M]

So does A2:B2 go in rows 7 & 8 as well ??
The prices in C are different !

 

[Buns1976]

Hi there,

A6 & B6 would go in A7, A8, B7 & B8!

Thank you!

 

[Michael M]

Maybe this then

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr
    If Cells(r, 1).Value = "" Then
        Range(Cells(r - 1, 1), Cells(r - 1, 2)).Copy Cells(r, 1)
    End If
Next r
End Sub

 

[Buns1976]

Michael,
I messed up! This works great in my test sheet where the first column is "A" however in the actual sheet the first column is "D".
I tried changing the macro to the following but that doesn't seem to work?

Thanks!

 Sub Merge()Dim lr As Long, r As Long
lr = Cells(Rows.Count, "D").End(xlUp).Row
For r = 2 To lr
    If Cells(r, 1).Value = "" Then
        Range(Cells(r - 1, 1), Cells(r - 1, 2)).Copy Cells(r, 1)
    End If
Next r
End Sub

 

[Michael M]

You need to change the column reference

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr
    If Cells(r, 4).Value = "" Then
        Range(Cells(r - 1, 4), Cells(r - 1, 5)).Copy Cells(r, 4)
    End If
Next r
End Sub

 

[DanteAmor]

Try the following:

Sub Macro1()
    With Range("D2:E" & Range("D" & Rows.Count).End(xlUp).Row)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
End Sub

 

[Buns1976]

THANK YOU Michael and Dante!

Much appreciated!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.