Efficient Way of Copy/PasteSpecial of Non-Continuous Range in VBA
 

Hi;

1) On a w/s I have:
No. of rows: cell C34

2) In an existing macro, I need to Copy a range in col H, and Paste and
PasteSpecial/Values of every 2nd cells in the range to col K.
a. Copy range starts at cell H44, down C34 rows
H44:: 1.00
H45:: = H44
H46:: = $H$44 - $G$35*$G46
H47:: = H46
H48:: = $H$44 - $G$35*$G48
H49:: = H48
H50:: = $H$44 - $G$35*$G50
..............................

b. Paste range starts at cell K44, down C34 rows, with:
PasteSpecial/Values of cells: H44, H46, H48, ... to cells: K44, K46, K48, ...
Paste of cells: H45, H47, H49, ... to cells: K45, K47, K49, ...

3) The implementation of the above looked horrible! Too many FOR loops and
too much repetition, since PasteSpecial doesn't apply to a non-continuous
range.

Can someone please advise on how to do it efficiently ??
Thank you kindly.

Efficient Way of Copy/PasteSpecial of Non-Continuous Range in VBA
 

For RowCount = 44 to 76 step 2
Range("H" & RowCount).Copy _
Destination:=Range("K" & RowCount)
Range("H" & (RowCount + 1)).Copy
Range("K" & (RowCount + 1)).PasteSpecial _
Paste:=xlPasteValues
Next RowCount

"monir" wrote:

Hi;

1) On a w/s I have:
No. of rows: cell C34

2) In an existing macro, I need to Copy a range in col H, and Paste and
PasteSpecial/Values of every 2nd cells in the range to col K.
a. Copy range starts at cell H44, down C34 rows
H44:: 1.00
H45:: = H44
H46:: = $H$44 - $G$35*$G46
H47:: = H46
H48:: = $H$44 - $G$35*$G48
H49:: = H48
H50:: = $H$44 - $G$35*$G50
.............................

b. Paste range starts at cell K44, down C34 rows, with:
PasteSpecial/Values of cells: H44, H46, H48, ... to cells: K44, K46, K48, ...
Paste of cells: H45, H47, H49, ... to cells: K45, K47, K49, ...

3) The implementation of the above looked horrible! Too many FOR loops and
too much repetition, since PasteSpecial doesn't apply to a non-continuous
range.

Can someone please advise on how to do it efficiently ??
Thank you kindly.

Efficient Way of Copy/PasteSpecial of Non-Continuous Range in
 

Joel;

Perfect! Thank you for your help.
(just need to swap the "Copy Distination" part of your code with the "Copy
PasteSpecial/Values" part to get the desired results.)

Thanks again. Greatly appreciated

"Joel" wrote:

For RowCount = 44 to 76 step 2
Range("H" & RowCount).Copy _
Destination:=Range("K" & RowCount)
Range("H" & (RowCount + 1)).Copy
Range("K" & (RowCount + 1)).PasteSpecial _
Paste:=xlPasteValues
Next RowCount

"monir" wrote:

Hi;

1) On a w/s I have:
No. of rows: cell C34

2) In an existing macro, I need to Copy a range in col H, and Paste and
PasteSpecial/Values of every 2nd cells in the range to col K.
a. Copy range starts at cell H44, down C34 rows
H44:: 1.00
H45:: = H44
H46:: = $H$44 - $G$35*$G46
H47:: = H46
H48:: = $H$44 - $G$35*$G48
H49:: = H48
H50:: = $H$44 - $G$35*$G50
.............................

b. Paste range starts at cell K44, down C34 rows, with:
PasteSpecial/Values of cells: H44, H46, H48, ... to cells: K44, K46, K48, ...
Paste of cells: H45, H47, H49, ... to cells: K45, K47, K49, ...

3) The implementation of the above looked horrible! Too many FOR loops and
too much repetition, since PasteSpecial doesn't apply to a non-continuous
range.

Can someone please advise on how to do it efficiently ??
Thank you kindly.

Efficient Way of Copy/PasteSpecial of Non-Continuous Range in
 

I didn't understand from your original posting why you needed a for loop
except for the fact that you request to use PasteSpecial Values on half the
lines and copying the cells straight on the other half. Since you are using
PasteSpecial on all the lines the code can be condensed to two lines


Range("H44:H77").Copy
Range("K44").PasteSpecial _
Paste:=xlPasteValues

"monir" wrote:

Joel;

Perfect! Thank you for your help.
(just need to swap the "Copy Distination" part of your code with the "Copy
PasteSpecial/Values" part to get the desired results.)

Thanks again. Greatly appreciated

"Joel" wrote:

For RowCount = 44 to 76 step 2
Range("H" & RowCount).Copy _
Destination:=Range("K" & RowCount)
Range("H" & (RowCount + 1)).Copy
Range("K" & (RowCount + 1)).PasteSpecial _
Paste:=xlPasteValues
Next RowCount

"monir" wrote:

Hi;

1) On a w/s I have:
No. of rows: cell C34

2) In an existing macro, I need to Copy a range in col H, and Paste and
PasteSpecial/Values of every 2nd cells in the range to col K.
a. Copy range starts at cell H44, down C34 rows
H44:: 1.00
H45:: = H44
H46:: = $H$44 - $G$35*$G46
H47:: = H46
H48:: = $H$44 - $G$35*$G48
H49:: = H48
H50:: = $H$44 - $G$35*$G50
.............................

b. Paste range starts at cell K44, down C34 rows, with:
PasteSpecial/Values of cells: H44, H46, H48, ... to cells: K44, K46, K48, ...
Paste of cells: H45, H47, H49, ... to cells: K45, K47, K49, ...

3) The implementation of the above looked horrible! Too many FOR loops and
too much repetition, since PasteSpecial doesn't apply to a non-continuous
range.

Can someone please advise on how to do it efficiently ??
Thank you kindly.