VBA: Copy values from non contiguous ranges to another of same size

P

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
Hi all,

I want to copy the values in ranges

G4:G9; G11:G20; G22:G25; G27:G39; G41:G44

to

H4:H9; H11:H20; H22:H25; H27:H39; H41:H44

as you can notice, above are exactly the same size and saw rows, only different columns.

The skpped rows (10, 21, 26, 40) contain subtotal formulas that I want to preserve.

I tried:

Code: 
Range("Range2").value = Range("Range1").value

Where range2 is the one in H column and range1 is the one in G column but is didn't copy properly. It sort of repeasted some values incorrectly.

What is the correct fastest way in VBA to do above? The ranges above are just partial list. In actual, I got more on each column and in several sheets. I feel that if I do the FOR EACH CELL IN RANGE, the code would take long.

Thanks
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
For a non-contiguous range, try like:
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> exa4()<br><SPAN style="color:#00007F">Dim</SPAN> Area<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Area <SPAN style="color:#00007F">In</SPAN> Range("G4:G9,G11:G20,G22:G25,G27:G39,G41:G44").Areas<br>        Area.Offset(, 1).Value = Area.Value<br>    <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
Hope that helps,

Mark
 
Upvote 0
I'm not suggesting that you should, but if you did want to do it without looping I think you can.

Assuming the values in G4:G9; G11:G20; G22:G25; G27:G39; G41:G44 are constants (ie not formulas) and G10, G21, G26 and G40 are formulas, you could try this (in a copy of your workbook)

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> myCopy()<br>    <SPAN style="color:#00007F">Dim</SPAN> rG <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rG = Range("G4:G44")<br>    <br>    Columns("H").Insert<br>    <SPAN style="color:#00007F">With</SPAN> rG<br>        .Copy Destination:=.Offset(, 1)<br>        <SPAN style="color:#00007F">With</SPAN> .Offset(, 1)<br>            .SpecialCells(xlCellTypeFormulas).ClearContents<br>            .Copy<br>            .Offset(, 1).PasteSpecial Paste:=xlPasteAll, _<br>                Operation:=xlNone, SkipBlanks:=True, Transpose:=<SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Columns("H").Delete<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

If my assumption was correct but the actual range can vary in the number of rows it occupies, that should be able to be accommodated too. Post back if you want to investigate this further.


If my assumption was incorrect, then you could try this version.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> myCopy2()<br>    <SPAN style="color:#00007F">Dim</SPAN> rG <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> Keep <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "H10, H21, H26, H40"<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rG = Range("G4:G44")<br>    Columns("H").Insert<br>    <SPAN style="color:#00007F">With</SPAN> rG<br>        .Copy Destination:=.Offset(, 1)<br>        Range(Keep).ClearContents<br>        <SPAN style="color:#00007F">With</SPAN> .Offset(, 1)<br>            .Copy<br>            .Offset(, 1).PasteSpecial Paste:=xlPasteAll, _<br>                Operation:=xlNone, SkipBlanks:=True, Transpose:=<SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Columns("H").Delete<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Last edited:
Upvote 0
Peter_SSs said:
Assuming the values in G4:G9; G11:G20; G22:G25; G27:G39; G41:G44 are constants (ie not formulas)
Click to expand...
Given that the source and destination ranges are cell-by-cell next to each other, and assuming only the values in Column G (not the formulas if there are formulas) should be copied to Column H, then this non-looping code should work...
Code: 
  With Range("G4:G9,G11:G20,G22:G25,G27:G39,G41:G44")
    .FormulaR1C1 = "=RC[1]"
    .Value = .Value
  End With
 
Last edited:
Upvote 0
Rick, very simple and I believe your assumptions are what the OP wants, though your code appears to be copying the wrong direction. I think the 'G' range should be 'H' and the formula "=RC[-1]"
 
Upvote 0
Peter_SSs said:
Rick, very simple and I believe your assumptions are what the OP wants, though your code appears to be copying the wrong direction. I think the 'G' range should be 'H' and the formula "=RC[-1]"
Click to expand...
Yep, you are right... I mixed up the ranges. Thanks for catching that. The code should be this instead...
Code: 
  With Range("H4:H9,H11:H20,H22:H25,H27:H39,H41:H44")
    .FormulaR1C1 = "=RC[-1]"
    .Value = .Value
  End With
 
Upvote 0
Hi,

Rick Rothstein said:
Code: 
  With Range("H4:H9,H11:H20,H22:H25,H27:H39,H41:H44")
    .FormulaR1C1 = "=RC[-1]"
    .Value = .Value
  End With
Click to expand...

I find that this code returns unexpected results for me. Debugging, I can see that the formula is correctly entered into each destination cell in the ranges but the .Value then applies values from the first range into the subsequent ranges. Example results are below showing Col G with some values I entered and Col H showing the results after running the code.

Col G Col H
<table border="0" cellpadding="0" cellspacing="0" width="128"><colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:13.5pt" height="18"> <td style="height:13.5pt;width:48pt" align="right" height="18" width="64">32</td> <td style="width:48pt" align="right" width="64">32</td> </tr> <tr style="height:13.5pt" height="18"> <td style="height:13.5pt" align="right" height="18">324</td> <td align="right">324</td> </tr> <tr style="height:13.5pt" height="18"> <td style="height:13.5pt" align="right" height="18">234</td> <td align="right">234</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">453</td> <td align="right">453</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">432</td> <td align="right">432</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">23</td> <td align="right">23</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">345</td> <td align="right">32</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">345</td> <td align="right">32</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2134</td> <td align="right">32</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">0</td> <td align="right">32</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">435</td> <td align="right">32</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">456</td> <td align="right">32</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">456</td> <td align="right">32</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">5476</td> <td align="right">32</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">67</td> <td align="right">32</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">76</td> <td align="right">32</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">789</td> <td align="right">32</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">789</td> <td align="right">324</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">45345</td> <td align="right">234</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">35</td> <td align="right">453</td> </tr> </tbody></table>
Can anyone confirm if this is the same for them? I'm using Excel 2003.

Thanks
 
Upvote 0
wirephobia said:
I find that this code returns unexpected results for me. Debugging, I can see that the formula is correctly entered into each destination cell in the ranges but the .Value then applies values from the first range into the subsequent ranges.

Can anyone confirm if this is the same for them? I'm using Excel 2003.
Click to expand...
My fault... I forgot about the skipped cells. If those skipped cells are empty, you can use this code...

Code: 
  Range("H4:H9,H11:H20,H22:H25,H27:H39,H41:H44").FormulaR1C1 = "=RC[-1]"
  Range("H4:H44").Value = Range("H4:H44").Value
If they are not empty, then you will have to use this instead...

Code: 
  With Range("H4:H9,H11:H20,H22:H25,H27:H39,H41:H44")
    .FormulaR1C1 = "=RC[-1]"
    For Each Ar In .Areas
      Ar.Value = Ar.Value
    Next
  End With
where Ar would be Dim'med as a Range variable.
 
Upvote 0
Thanks Rick....in my application the intervening cells are non-blank, so looks like I will have to stick with the for each type of approach I have.

Purely out of interest, do you understand what is happening to cause the results I posted in the simple example above? (I don't understand)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Print from database
Replies
0
Views
278
Charlreena
C
K
  • Question Question
vba copying unique values of a range to another workbook.
Replies
1
Views
760
k_bs
K
M
  • Solved
Copy cell color to another sheet
Replies
6
Views
370
mumps
mumps
W
  • Question Question
Trying to learn how to loop code
Replies
1
Views
237
mumps
mumps
D
  • Question Question
VBA for Copying & pasting (with resizing) Named ranges from one workbook to another
Replies
3
Views
768
doctorhifi
D

Forum statistics

Threads
1,224,534
Messages
6,179,391
Members
452,909
Latest member
VickiS

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top