# Thread: Help with a macro please Thanks: 0 Likes:  1 Post #5262885 (1)

1. ## Help with a macro please

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

2. ## Re: Help with a macro please

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

3. ## Re: Help with a macro please

Hi there,

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

Thank you!

4. ## Re: Help with a macro please

Maybe this then
Code:
```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```

5. ## Re: Help with a macro please

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!

Code:
``` 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```

6. ## Re: Help with a macro please

You need to change the column reference

Code:
```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```

7. ## Re: Help with a macro please

Try the following:

Code:
```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```

8. ## Re: Help with a macro please

THANK YOU Michael and Dante!

Much appreciated!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•