Build macro routine

[Closed]
Report
Posts
2
Registration date
Wednesday September 25, 2013
Status
Member
Last seen
October 3, 2013
-
 gchancga -
I've tried but failed.
Can anyone build an excel macro just to copy a row of 3 cells and paste down the blank cells until the next filled cell, then starts (loop) again from that 'filled' cell and the loop ends when it reaches the next blank cell?
thank you

3 replies

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Gchancga,

Not clear.

"copy a row of 3 cells " You mean A1:C1?
Destination: ???

Explain in more detail.

Best regards,
Trowa
HI
Tx for the help! Yes copy A1:C1, to all the blanks cells below and stop at the next non-blank cell, and start the routine again, copy from those 'non-blank' cells downwards to all the blank cells, and the loop stops when it reaches a blank content! My macro recording seem to always copies over the non-blank cell, I just need the paste range to move back up one row, right?
Hi
Thanks for the suggestion, it's a good start! One question, I do not know where my paste destination ends? It should end immediately before the next cell with content! I think I need to test for the last blank cell down the column and store it in a variable to use for range parameter?

Tx
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Gchancga,

Give this code a try:
Sub RunMe()
For Each cell In Range("A1:A20") 'Adjust range to match yours
If cell.Value <> vbNullString Then
Range(cell, cell.Offset(0, 2)).Copy
Else: cell.PasteSpecial
End If
Next cell
Application.CutCopyMode = False
End Sub

Best regards,
Trowa
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Gchancga,

If you mean that the last cell with content should not be copied down then try this code:
Sub RunMe()
Dim lRow As Integer
lRow = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A1:A" & lRow)
If cell.Value <> vbNullString Then
Range(cell, cell.Offset(0, 2)).Copy
Else: cell.PasteSpecial
End If
Next cell
Application.CutCopyMode = False
End Sub

Best regards,
Trowa
Yes, genius! The concept works, I just need to tweak the relative reference range and figure out how to loop it and exit or end loop when the active cell is blank! Any suggestions? Tx
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Sorry gchancga, I failed to understand you.

An active cell is the cell you have selected. Do you mean that you want to loop until the pre-selected (active) cell is reached?

If this is indeed what meant then change:
lRow = Range("A" & Rows.Count).End(xlUp).Row
into:
lRow = ActiveCell.Row

Best regards,
Trowa
Finally figure it out, (just need to loop it).

Sub selectArange()
' selectArange copy and paste Macro
'
'
Dim area As Integer
Range(ActiveCell, ActiveCell.End(xlDown)).Select
area = Selection.Rows.Count
Selection.Resize(area - 1, 3).Select
Selection.FillDown
ActiveCell.Offset(area - 1, 0).Select
End Sub