Better way than Range(Selection,Selection.End(xlDown)).copy ?

[JamesonMH]

Still learning the VBA ropes and I know "select" s/b avoided whenever possible, so I'm posting this.

Assuming data is in Range("C136:E139") and I want to copy the data in D136:D139 I used the code Range(Selection,Selection.End(xlDown)).copy which was straight from the macro recorder...

Is there a more efficient way?

Only other way I came up with was: Range("C136").currentregion.offset(,1).resize(,1).copy

Thanks

James

 

[Michael M]

Is the initial dataset correct
Range("C136:E139") ??

Range("D136:D139").copy

 

[AlphaFrog]

Assuming data is in Range("C136:E139") and I want to copy the data in D136:D139

Try this...

Range("C136", Range("C136").End(xlDown)).Offset(0,1).Copy

 

[JamesonMH]

Try this...

Range("C136", Range("C136").End(xlDown)).Offset(0,1).Copy

Thanks AlphaFrog, that's what I was after. I didn't realize Range could be written like that.

Too bad the macro recorder uses "select/selection" all over the place instead of code like you just shared. Good thing for this forum!

Cheers,
James

 

[JamesonMH]

Is the initial dataset correct
Range("C136:E139") ??

Range("D136:D139").copy

Sorry if I wasn't too clear what I was after in my original post, Michael. I was looking for a concise way of doing the equivalent of Ctrl+Shift+arrow without using the select/selection that the macro recorder uses.

AlphaFrog posted what I was looking for so all is good.

Cheers
James