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

[PATSYS]

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:

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

 

[Rick Rothstein]

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)

It has to do with the non-contiguous ranges... you can do mass value copying to contiguous cells only... when there are breaks, Excel tries to start over again.

 

[asad]

  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.

I found this post after searching a lot. This has saved my life
One question though, how do I define Range variable. Is it same as "Dim Ar as Range" or something else?

 

[Rick Rothstein]

I found this post after searching a lot. This has saved my life
One question though, how do I define Range variable. Is it same as "Dim Ar as Range" or something else?

I am surprised at myself... I always try to indicate the data type for the variables I use, even in code snippets like you quoted, but I see I did not in this particular thread. Oh well. Anyway, yes, you are correct... Ar should be Dim'med as Range.

 

[asad]

Thanks Rick.