CODEWITHSUNDEEP
vbaexcelrange
NewtoCompSci
NewtoCompSci

Reputation: 27

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

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.

Upvotes: 1

Views: 1962

Answers (2)

BruceWayne
BruceWayne

Reputation: 23283

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

Upvotes: 1

user4039065
user4039065

Reputation:

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.

Upvotes: 3

Related Questions