Range in Excel to go from a specific cell to the end of the column

Question

I am wondering how to define a range in order for it to be define from C5 until the last value of the column. C5 is a constant but the end of the list isn't.

Sub Merge()
Worksheets("Sub").Range("C5").End(xlToLeft).Select.copy
Worksheets("Master Worksheet").Range("A1:A23").PasteSpecial Paste:=xlPasteValues

End Sub

I am getting a subscript out of range error. Any guidance would be really nice.

Answer 1

You don't have to copy and paste special, values. Transfer the values over directly and avoid using the clipboard.

This could probably use one or two variable assignments to clean up the code.

With Worksheets("Sub")
    Worksheets("Master Worksheet").Range("A1") _
      .Resize(.Range(.Cells(5, 3), .Cells(5, Columns.Count).End(xlToLeft)).Columns.Count, 1) = _
      Application.Transpose(.Range(.Cells(5, 3), .Cells(5, Columns.Count).End(xlToLeft)).Value)
End With

1. The code is a little long because we need to resize the destination in order to receive the values. As mentioned, one or more vars would clean it up tidily.
2. When creating a Range(Cells, Cells.End) type cell range reference, MAKE SURE that the Range.Cells property inside the Range object know what their Range .Parent property is. In this case, I've used a With...End With statement and prefixed the .Cells with a period (aka full stop) so that they know that they belong to Worksheets("Sub") .
3. Use Application.Transpose to flip the columns of values into rows.

Answer 2

You could do: Worksheets("Sub").Range(Cells(5,3),Cells(5,Cells(5,3).End(xltoLeft).column)).Select

This will start at cell C5 (aka Cells(5,3)), and go left.

edit: As Jeeped points out, it might be better to do:

With Worksheets("Sub")
    .Range(.Cells(5,3),.Cells(5,.Cells(5,3).End(xlToLeft).Column)).Select
End With