Trouble in VBA with Range().Value

[cr731]

I have a worksheet with certain columns containing formulas that I want to hardcode and store only the values. To do this, I created a named range containing the cells and then in VBA performed this operation:

Sheet1.Range("MyRange").Value = Sheet1.Range("MyRange").Value

However, this causes some issues. The problem seems to be that my range ("MyRange") contains cells that are not all adjacent to each other. So, for example, part of my range is in column C, and then another part of it in column E (but nothing in column D). The issue seems to be that it only keeps the values of the left-most cells, so it would replace what is in column E with data from column C.

Is there something I'm doing wrong with this code? Or can .Value not be used in ranges that contain non-adjacent cells?

Thanks

 

[Joe4]

If you loop thought your range once cell at a time, it should work, i.e.:

    Dim cell As Range
    For Each cell In Sheet1.Range("MyRange")
        cell.Value = cell.Value
    Next cell

 

[Jonmo1]

Perhaps even loop through each area of the range..

    Dim c As Range
    For Each c In Sheet1.Range("MyRange").Areas
        c.Value = c.Value
    Next c