Copy last populated cell of column A to the next empty cell below to the last row of E

[ahuang3433]

Hi,

I am trying to achieve the following.

- Copy last populated Cell of each of the designated columns and paste up until the last populated row based on Column E referencing.

Current State:

It paste over everything.

'Copy Data Down (Columns A:D,F:J, & L)

    Dim lastRow2 As Long
    lastRow2 = Range("E" & Rows.Count).End(xlUp).Row
    Range("A1").End(xlDown).Copy Destination:=Range("A2:A" & lastRow2).Offset(1, 0)
    Range("B1").End(xlDown).Copy Destination:=Range("B2:B" & lastRow2).Offset(1, 0)
    Range("C1").End(xlDown).Copy Destination:=Range("C2:C" & lastRow2).Offset(1, 0)
    Range("D1").End(xlDown).Copy Destination:=Range("D2:D" & lastRow2).Offset(1, 0)
    Range("F1").End(xlDown).Copy Destination:=Range("F2:F" & lastRow2).Offset(1, 0)
    Range("G1").End(xlDown).Copy Destination:=Range("G2:G" & lastRow2).Offset(1, 0)
    Range("H1").End(xlDown).Copy Destination:=Range("H2:H" & lastRow2).Offset(1, 0)
    Range("I1").End(xlDown).Copy Destination:=Range("I2:I" & lastRow2).Offset(1, 0)
    Range("J1").End(xlDown).Copy Destination:=Range("J2:J" & lastRow2).Offset(1, 0)
    Range("L1").End(xlDown).Copy Destination:=Range("L2:L" & lastRow2).Offset(1, 0)
    Range("O1").End(xlDown).Copy Destination:=Range("O2:O" & lastRow2).Offset(1, 0)

 

[Joe4]

Try this:

Sub MyCopy()

    Dim cols()
    Dim c As Long
    Dim i As Long
    Dim col As String
    Dim lastRow1 As Long
    Dim lastRow2 As Long
    
    Application.ScreenUpdating = False
    
'   Set array of columns to apply this to
    cols = Array("A", "B", "C", "D", "F", "G", "H", "I", "J", "L", "O")
    
'   Find last row in column E with data
    lastRow2 = Cells(Rows.Count, "E").End(xlUp).Row
    
'   Loop through columns
    For i = LBound(cols) To UBound(cols)
        col = cols(i)
'       Find last row with data in current column
        lastRow1 = Cells(Rows.Count, col).End(xlUp).Row
'       Copy down to rest of row, if necessary
        If lastRow2 > lastRow1 Then
            Cells(lastRow1, col).Copy Range(Cells(lastRow1 + 1, col), Cells(lastRow2, col))
        End If
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

 

[ahuang3433]

Try this:

Sub MyCopy()

    Dim cols()
    Dim c As Long
    Dim i As Long
    Dim col As String
    Dim lastRow1 As Long
    Dim lastRow2 As Long
   
    Application.ScreenUpdating = False
   
'   Set array of columns to apply this to
    cols = Array("A", "B", "C", "D", "F", "G", "H", "I", "J", "L", "O")
   
'   Find last row in column E with data
    lastRow2 = Cells(Rows.Count, "E").End(xlUp).Row
   
'   Loop through columns
    For i = LBound(cols) To UBound(cols)
        col = cols(i)
'       Find last row with data in current column
        lastRow1 = Cells(Rows.Count, col).End(xlUp).Row
'       Copy down to rest of row, if necessary
        If lastRow2 > lastRow1 Then
            Cells(lastRow1, col).Copy Range(Cells(lastRow1 + 1, col), Cells(lastRow2, col))
        End If
    Next i
   
    Application.ScreenUpdating = True
   
End Sub

This didn't quite work. It needs to find the last populated cell in column A,B,C,D,F,etc. and copy it and paste all the way down until the last populated row based on Column E. Let me know if this is confusing.

 

[Fluff]

That's what Joe's code is doing. In what way isn't it working for you?

 

[ahuang3433]

That's what Joe's code is doing. In what way isn't it working for you?

it didn't copy anything on the Columns that were designated. I theory it should take row A70 which is the last row populated and copy that value and paste on A71 to A80 since Row 80 is the last populated row based on column "E".

 

[Fluff]

Do those columns contain formulae?

 

[ahuang3433]

Do those columns contain formulae?

No they don't. All values.

 

[Fluff]

Do any of those columns have values below the last used cell in col E?

 

[ahuang3433]

Do any of those columns have values below the last used cell in col E?

Nope.

 

[Fluff]

In that case I can see no reason why Joe's code shouldn't work.
Try stepping through it using F8 & see if the code ever gets to this line

Cells(lastRow1, col).Copy Range(Cells(lastRow1 + 1, col), Cells(lastRow2, col))