Can someon explain this: ("A1:G" & [A65536].End(xlUp).Row)

[cardinaluk]

Hi,

I have recently discovered this wonderful code snippet:

Of course it transfers rows of data from one location to another, catering for any blank rows.

Sub TransferData()
Dim src As Range, dest As Range
    Set src = ActiveSheet.Range("A1:G" & [A65536].End(xlUp).Row)
    Set dest = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    src.Copy Destination:=dest

End Sub

For my complete understanding can someone talk me through it, specifically this bit:

Range("A1:G" & [A65536].End(xlUp).Row) - what I really don't understand is the inclusion of an & instead of a comma. Is this just the syntax when you are using square brackets?

I would also like to understand in the last line src.Copy destination:=dest, what does the := mean?

I also need to know why you need to set the destination using a similar bit of code? I would have thought the set source would be enough?

Regards,


C

 

[Norie]

& is the concatenation operator which is being used here to concatenate the row number returned by [A65536].End(xlUp).Row to "A1:G" to complete the range reference.

:= is used when working with named arguments and it assigns/sets the value on the right to the named argument on the left.

So in the posted code it's used to set the Destination of the copy to the range dest.

src and dst are on different worksheets and they point to different ranges, src to the entire range to copy and dst to the single cell to copy to.

 

[cardinaluk]

& is the concatenation operator which is being used here to concatenate the row number returned by [A65536].End(xlUp).Row to "A1:G" to complete the range reference.

:= is used when working with named arguments and it assigns/sets the value on the right to the named argument on the left.

So in the posted code it's used to set the Destination of the copy to the range dest.

src and dst are on different worksheets and they point to different ranges, src to the entire range to copy and dst to the single cell to copy to.

Regarding your last answer - does the Set dest statement require the below code:

Cells(Rows.Count, 1).End(xlUp).Offset(1,0)?

btw: A great explanation thank you.

 

[Norie]

Yes it needs that to set the range, dest, to copy to.

In words what the code is doing is copying columns A:G on the active sheet from row 1 to the last row of data to the next empty cell in column A on Sheet2.