PDA

View Full Version : Solved: Rewrite this line of code



YellowLabPro
06-08-2006, 04:27 AM
In words, Select data starting at P4 down, copy, paste special.values to column O starting at 4 down.

This was recorded:
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("O4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

This is what I desire to happen:
Range("P4:P").Selection.End(xlDown).Select
Selection.Copy
Range("O4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


thx,

ylp

Edited 9-Jun-06 by geekgirlau. Reason: insert vba tags

Killian
06-08-2006, 04:56 AM
Can be done in one line if you refer directly to the ranges rather than selecting them first (as recorded) and use the "destination" argument of the Copy methodRange(Range("P4"), Range("P4").End(xlDown)).Copy Range("O4")

YellowLabPro
06-08-2006, 04:58 AM
Killian,
Thanks. Follow-up.
The line of code did partially what I needed it to do. It needs to paste.special.values and the way it is written now is it only pastes. Can you edit?
Also:
I have read about selecting from the bottom up. Finding the last cell w/ data and copying up. This would help I think, my data changes frequently, and I am running into issues w/ either missing data or bombing my procedure.

ylp

Killian
06-08-2006, 06:12 AM
Well if you have any gaps in the data, then xlDown may not be capturing the entire range you want. I might be better to use xlUp.
I've also defined the ranges here to make it a little clearerDim rngSource As Range
Dim rngtarget As Range

Set rngSource = Range("P4")
Set rngtarget = Range("O4")

Range(rngSource, Cells(Rows.Count, rngSource.Column).End(xlUp)).Copy rngtarget

YellowLabPro
06-08-2006, 06:15 AM
Thanks,
I edited my reply to you, can you look at that?

Killian
06-08-2006, 06:39 AM
Ahh yes, my bad... PasteSpecial
In that case, rather than using the target as an argument to copy (standard paste) it just need to be used in a new line as the subject of PasteSpecialSet rngSource = Range("P4")
Set rngtarget = Range("O4")

Range(rngSource, Cells(Rows.Count, rngSource.Column).End(xlUp)).Copy
rngtarget.PasteSpecial xlPasteValues

YellowLabPro
06-09-2006, 10:11 AM
Killian,
Thanks. This got me going in the right direction.

Have a great one.

YLP