excelvbaoffset

VBA - Copy column data to new column, if data exists, use column to the right

I would like to copy a column's data (Example: A2:A10) to C2:C10. However, if C2:C10 already has data, I would like it to copy the data to the next column to the right. In this example that would be D2:D10.
How would I achieve this?
Thank in advance

Solution 
  • Option Explicit

Sub CopyCol()

    Dim rngFrom As Range, rngTo As Range
    With ActiveSheet ' or Sheets(1) or Sheets("Sheet1")
        Set rngFrom = .Range("A2:A10")
        Set rngTo = rngFrom.Offset(, 2)
        Do While WorksheetFunction.CountA(rngTo) > 0
           Set rngTo = rngTo.Offset(, 1)
        Loop
        rngFrom.Copy rngTo
    End With
    
End Sub