This might help. It will tell you what the last row is, then you can simply
copy the data from the current place to the new place:
Function iLastRowFilledInColumn(ws As Excel.Worksheet, iColumn As Long) As
Long
Dim iLastRow As Long
iLastRow = ws.Cells(ws.Rows.Count, iColumn).End(xlUp).Row
'
' If the entire column is empty, Excel still returns 1 even though
' there is no data in row 1. Therefore, check to see if the
' cell is empty; if it is, return 0 instead of 1.
'
If iLastRow = 1& Then
If Trim(ws.Cells(iLastRow, iColumn)) = "" Then
iLastRow = 0&
End If
End If
iLastRowFilledInColumn = iLastRow
End Function