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
[TABLE="width: 624"]
<tbody>[TR]
[TD]Row1-00000000010054/000[/TD]
[TD]20 OZ FOUNTIAN[/TD]
[TD]$1.19[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]$5.95[/TD]
[/TR]
[TR]
[TD]Row2-00000000010078/000[/TD]
[TD]32 OZ FOUNTIAN[/TD]
[TD]$1.39[/TD]
[TD]21[/TD]
[TD]21[/TD]
[TD]$29.19[/TD]
[/TR]
[TR]
[TD]Row3-[/TD]
[TD][/TD]
[TD]$1.19[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1.19[/TD]
[/TR]
[TR]
[TD]Row4-[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22[/TD]
[TD]$30.38[/TD]
[/TR]
[TR]
[TD]Row5-00000000010511/000[/TD]
[TD]Refill Fntn[/TD]
[TD]$0.79[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]$8.69[/TD]
[/TR]
[TR]
[TD]Row6-00000000010528/000[/TD]
[TD]44 OZ FOUNTIAN[/TD]
[TD]$1.29[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]$3.87[/TD]
[/TR]
[TR]
[TD]Row7-[/TD]
[TD][/TD]
[TD]$1.49[/TD]
[TD]17[/TD]
[TD]21[/TD]
[TD]$31.29[/TD]
[/TR]
[TR]
[TD]Row8-[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]24[/TD]
[TD]$35.16[/TD]
[/TR]
[TR]
[TD]00000000011037/000[/TD]
[TD]16oz Coffee[/TD]
[TD]$1.39[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]$5.56[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.