Copy/Paste from next empty cell to last empty cell

[josephmary]

Hi,
I know this may be simple but I only know a few macros so I had to ask a question
As shown on the image, I need U2 to U12 (which is the sections 1.1 to 4.2) be copied and pasted from next empty cell up to the last empty cell

 

[Micron]

Don't know if you realize, but this can be done by highlighting the range to be copied (from first cell to last) then click on the +sign in the corner of the last cell, hold ctrl key and drag down.

 

[josephmary]

Apologies if I wasn't clear enough. I wanted to write a macro that will do that automatically for me

 

[josephmary]

but i think i get what you're saying. i can just record the macro. i will try and get back to you. Thank you

 

[Joe4]

That range of cells, from U2 to U12, is exactly 11 cells.
When copy down this pattern, will the number of rows to copy to ALWAYS be an exact multiple of 11 (so you would also be copying a full set of these 11 values each time)?

 

[josephmary]

that's right

 

[Joe4]

Try this:

Sub MyCopy()

    Dim lrT As Long
    Dim rng As Range
    Dim r1 As Long
    Dim r2 As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column T with data
    lrT = Cells(Rows.Count, "T").End(xlUp).Row
    
'   Set range to copy from
    Set rng = Range("U2:U12")
    
'   Set initial values of r1 and r2
    r1 = 2
    r2 = 12
    
'   Loop until copied down enough times
    Do
'       Exit if copied down to last row of column T
        If lrT <= r2 Then
            Exit Sub
        End If
'       Increment row numbers by 11
        r1 = r1 + 11
        r2 = r2 + 11
'       Copy down
        rng.Copy Cells(r1, "U")
    Loop
    
    Application.ScreenUpdating = True
        
End Sub

 

[josephmary]

What's r1 and r2 for?

 

[Joe4]

What's r1 and r2 for?

Just to track the 11 rows we are copying to (r1 is the starting row, r2 is the ending row for each loop).

 

[josephmary]

got it. Thanks! it worked wonders!