Loop thru Mult Ranges and save as valus

[Grimm127]

Hello Gurus!

I am a VBAmature and in need of your genius, lol.


I have a set of various ranges that I need to set to save as values. My current set up as follows. However the problem is that I can't paste values in multiple ranges.

So I need something that loops through each range and saves those ranges as values. How can I do this?

'PNL1
Range("A6:AC8,A10:AC11,A13:AC13,A16:AC19,A21:AC22,A25:AC28,A30:AC30,A298:AC300,A302:AC303,A305:AC305,A308:AC311,A313:AC314,A317:AC320,A322:AC322").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[Fluff]

How about

Sub Tstt()
    
    Dim Cl As Range

    For Each Cl In Range("A6:AC8,A10:AC11,A13:AC13,A16:AC19,A21:AC22,A25:AC28,A30:AC30,A298:AC300,A302:AC303,A305:AC305,A308:AC311,A313:AC314,A317 :AC320,A322:AC322")
        Cl.Value = Cl.Value
    Next Cl
    
End Sub

 

[PatOBrien198]

perhaps something like....

Sub test()
Dim c As Range
Dim rng As Range
Set rng = Range("d7:f12,g1:h21")
For Each c In rng.Cells
    c = c.Value
Next
End Sub

 

[Grimm127]

How about

Sub Tstt()
    
    Dim Cl As Range

    For Each Cl In Range("A6:AC8,A10:AC11,A13:AC13,A16:AC19,A21:AC22,A25:AC28,A30:AC30,A298:AC300,A302:AC303,A305:AC305,A308:AC311,A313:AC314,A317 :AC320,A322:AC322")
        Cl.Value = Cl.Value
    Next Cl
    
End Sub

Thank you for the suggestion, the problem I am finding is that its going cell by cell to convert to values and its taking too long. It does work however.

 

[Grimm127]

Thank you for the suggestion.
But like the other code since its going cell by cell to covert to values its taking too long to calc. Is there any other options? It does work as you have it.

 

[mikerickson]

When working with discontinuous ranges, one can use Areas

Dim oneArea as Range

For Each oneArea in  Range("A6:AC8,A10:AC11,A13:AC13,A16:AC19,A21:AC22,A25:AC28,A30:AC30,A298:AC300,A302:AC303,A305:AC305,A308:AC311,A313:AC314,A317 :AC320,A322:AC322").Areas
    oneArea.Value = oneArea.Value
Next oneArea

Another option would be to do the whole included range at one go, if the intermediate cells (AC9 for example) do not have formulas

With  Range("A6:AC8,A10:AC11,A13:AC13,A16:AC19,A21:AC22,A25:AC28,A30:AC30,A298:AC300,A302:AC303,A305:AC305,A308:AC311,A313:AC314,A317 :AC320,A322:AC322")
    With Range(.Cells, .Cells)
        .Value = .Value
    End With
End With

 

[Grimm127]

Wow! I never heard of Areas. It works perfectly. Thank you for the solution. I really appreciated it!!

 

[PatOBrien198]

I usually start and end cell updating routines by chaning the Calculation mode....
before the loop:

Application.Calculation = xlCalculationManual

then after the loop:

Application.Calculation = xlCalculationAutomatic

 

[Grimm127]

I usually start and end cell updating routines by chaning the Calculation mode....
before the loop:

Application.Calculation = xlCalculationManual

then after the loop:

Application.Calculation = xlCalculationAutomatic

Will that improve the timing issue? The number of ranges I have are 60 per table, with 7 tables in each tab

 

[PatOBrien198]

Yes. Will it be noticeable? It depends on how many formulas you have in your workbook... how long it takes to recalculate the whole workbook...