CODEWITHSUNDEEP
excelvba
ncarroll
ncarroll

Reputation: 118

Copy data up to last used column with vba

I was successfully able to copy data up to the last used row using VBA. I am trying to do the same thing but copy data from A1 to LastColumn2. Here is the code I have put together thus far:

Sheets("Results").Select
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Range("A1:" & LastColumn & "2").Select
Selection.Copy

The debugger highlights the third line. This is just a portion of the code - All of the variables have been dimensioned properly.

Upvotes: 1

Views: 14640

Answers (2)

bto.rdz
bto.rdz

Reputation: 6720

The problem is that the range you are passing is wrong because it is wating simething like:

Range("A1:C2").Select

and you are passing:

Range("A1:32").Select

So what you can do is:

Range(cells(1,1),cells(2,lastcolumn)).Select

Cell(1,1) = A1 beacuse its is row number 1 column number 1

As mentioned it is better if you just 

Range(cells(1,1),cells(lastcolumn,2)).copy

Hope it helps

Upvotes: 3

Siddharth Rout
Siddharth Rout

Reputation: 149335

You are getting the error because LastColumn is number. You want the string equivalent of it i.e the column name. For Further Reading

Avoid the use of .Select and fully qualify your objects. INTERESTING READ

Is this what you are trying?

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim LastCol As Long
    Dim LastColumn As String

    Set ws = ThisWorkbook.Sheets("Results")

    With ws
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        '~~> Return column name from number
        LastColumn = Split(.Cells(, LastCol).Address, "$")(1)

        Set rng = .Range("A1:" & LastColumn & "2")

        Debug.Print rng.Address

        rng.Copy
    End With
End Sub

Upvotes: 3

Related Questions