Range copy problem

[JimDandy]

I have a worksheet where I would like to copy a non-contiguous range of cells to another column, then copy another range back into the first range. The range has some restrictions where certain cells in the range cannot be copied over (these would be calculated fields), and I only want to copy the values, not the cell formats.

I was hoping to set the range to a non-contiguous range of cells and then use "rng3.Value = rng1.Value" but apparently you can't copy non-contiguous ranges...at least not like this.

I have attached an example worksheet if someone could show me how to obtain the example result in the worksheet.

[watersev]

hi JimDandy, option

Please Login or Register  to view this content.

[watersev]

or

Please Login or Register  to view this content.

[jolivanes]

Please Login or Register  to view this content.

See attached

[JimDandy]

Wow, that attachment was really close however the skipped cells will also have numbers in them. I only meant to show that I needed to skip them.

[watersev]

if A4 and A6 are formulas and the rest are numbers:

Please Login or Register  to view this content.

If not please post exact data and colour cells to skip and how to define them

[jolivanes]

How many cells do you intend to skip?
If it is just 2, then this would work but if there are a lot of cells to be skipped it will be a different story.

Please Login or Register  to view this content.

No doubt someone will come with a better solution (put the cells to be skipped in an array maybe)

[JimDandy]

Yes, that did it...thanks

I also found this solution

With Range("C1:C3,C5,C7:C8")
.FormulaR1C1 = "=RC[-2]"
For Each Ar In .Areas
Ar.Value = Ar.Value
Next
End With

[JimDandy]

Here is the attachment with both solutions should anyone want to experiment.

Thanks to everyone that helped...