| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| efficient code to copy/paste | Excel Programming | |||
| More efficient copy/paste?? | Excel Programming | |||
| Efficient way to copy a range in numerous sheets within a workbook | Excel Programming | |||
| Efficient Copy/Paste | Excel Programming | |||
| How To Copy & PasteSpecial: Range from Mult Wksht into SummarySheet | Excel Programming | |||
Linear Mode
