Apply rounding to data body range in table

[Costas]

Hi Chandoo,

I've got a massive table of 25 columns and 50,000 rows which I'm creating by copying and pasting selected columns from a larger raw data table.

I'd like to round to 2 decimals the values that I'm copying from the raw data table either during the copy/paste process or afterwards when the columns are pasted in my working table.

The only way I found to be working is to loop through each row in a column and round the raw value with this code

For Each MyCell In MyObject.ListColumns(12).DataBodyRange
        MyCell.Value = Round(MyCell, 2)
Next MyCell

This process takes at least an hour to run per column so I was wondering if there is a quicker way.

I've already tried

MyObject.Range.Columns(12) = Round(MyObject.Range.Columns(12).Value, 2)

and

Sheets("GDB").Range("Tbl_GDB[Cost (Ex)]").Copy
Round(MyObject.DataBodyRange(1, 12).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False,2)

but they don't work.

Any suggestions?

 

[Costas]

Update

I've just created a spare column at the end and tried this code:

    MyObject.DataBodyRange.Columns(26).FormulaR1C1 = "=round([@Cost],2)"
    Application.Calculate
    MyObject.DataBodyRange.Columns(12) = MyObject.DataBodyRange.Columns(26).Value
    MyObject.DataBodyRange.Columns(26).ClearContents

It works really well and only takes a few seconds to run.

Unless you have another suggestion, please consider this closed.

 

[vletm]

Costas
You were hurry.
You could wait whole 49 minutes.
That could be a short moment for others without an Excel sample file to test and verify Your case.
It's good that You find Yourself a solution.