Copy Column to Row without hard coding cell references

[jmartineau]

Hey!

I'm trying to move the data from one column to "a" row. The row is determined by the size of the data set but will always be two rows below the last row of the data. I need to place the data from the column in that row (the column is hard coded because it will never interfere). In the code below, I take a column in the data set, find the end of it, and make the activecell 2 rows below (where the row needs to begin).

here is the code i have so far:

Lastrow = Range("B:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlDown).row
Range("B1:B2" & Lastrow).Select
ActiveCell.Offset(2, 0).Select
    
Count = Range(Range("CA1"), Range("CA2").End(xlDown)).Count
Range(ActiveCell).Resize(, Count).value = Application.Transpose(Range(Range("CA1"), Range("CA1").End(xlDown))

Can someone please provide the code to fix this?

Thanks a ton in advance!