Previous Topic Next Topic

Build macro routine

Closed
gchancga Posts 2 Registration date Wednesday September 25, 2013 Status Member Last seen October 3, 2013 - Sep 25, 2013 at 02:14 AM
 gchancga - Oct 11, 2013 at 03:59 AM
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
Related:

3 responses

Answer 1 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Sep 26, 2013 at 11:21 AM
Hi Gchancga,

Not clear.

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

Explain in more detail.

Best regards,
Trowa
gchancga
Sep 26, 2013 at 01:49 PM
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?
gchancga
Sep 30, 2013 at 01:10 PM
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
Answer 2 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Sep 30, 2013 at 10:26 AM
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
Answer 3 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Oct 1, 2013 at 10:33 AM
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
gchancga
Oct 1, 2013 at 02:24 PM
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Oct 3, 2013 at 10:34 AM
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
gchancga
Oct 11, 2013 at 03:59 AM
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

Similar discussions

hard disk not detected in bios Intel UNDI PXE-2.1 build 083
keko1994 -
keko1994 -

2 responses
Automatically Run Macro from a calculated cell value change
smiller -
TrowaD -

1 response