Excel slow when rounding a huge range of cells (VBA)

Question

I have given a task of rounding a table with 119col*176Rows = 20944 (could be more) cells with double numbers. This comes from a measure machine with a lots of measurements and calculations. My first Idea was to make a simple vba to update all cells in that range with the rounded number one by one in a loop, like shown in code below:.

BUT... this is Soooooooo Sloww that even Excel application crashes. I just need the same worksheet but values rounded to 3 decimals (the rounding numbers from Format Cells feature from Excel does get the job in this case, I need the numbers rounded and cut till third decimal in for each value)

Any workaround would be really appreciated guys

   Sub Round_numbers_click()
        Dim rng As Range
        Dim rCell As Range
    
        Dim valOriginal As Double
        Dim valRounded As Double
        
        Range("E15").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        
        Set rng = Application.Selection
        
        For Each rCell In rng.Cells
            valOriginal = col.Cells.Value
            valRounded = Round(valOriginal, 3)
            rCell.Value = valRounded
        Next rCell
    End Sub

Do you really want to round the numbers or is it just a matter of formatting? With other words:Do you really want to loose the precision? If yes, copy the data into an array (see for example https://stackoverflow.com/a/59210282/7599798), loop over that, round the values and write the array back into the sheet, that should dramatically speed up the process. If no, simply set the number format of the cells — FunThomas, Apr 28 '21 at 15:35
col.cells.value looks like a typo. Should it be rcell.value? Also try using value2 instead of value. Value stores the number format in memory, value2 does not — Dude_Scott, Apr 28 '21 at 15:52
Note that `Round` uses bankers rounding. If you don't want that, use `WorksheetFunction.Round`. — BigBen, Apr 28 '21 at 15:56
20,000 entries is not that many, so it shouldn't really take that long. Are you sure the sheet is healthy and doesn't contain a lot of "deleted" rows? — Senior Momentum, Apr 28 '21 at 16:11

score 0 · Answer 1 · answered Apr 28 '21 at 16:08

Save the range to a variant array, process the array and save the array back to the range.

Sub Round_numbers_click()
        Dim rng As Range
        Dim rCell As Range
        Dim vArr As Variant
        Dim iRow As Long, iCol As Long
    
        Dim valOriginal As Double
        Dim valRounded As Double
        
        Range("E15").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        
        Set rng = Application.Selection
        vArr = rng
        For iRow = 1 To UBound(vArr, 1)
            For iCol = 1 To UBound(vArr, 2)
                vArr(iRow, iCol) = Round(Val(vArr(iRow, iCol)), 3)
            Next
        Next
        rng = vArr
    End Sub

I can see it is very fast and like your logic but is not working. every two columns is repeating the same numbers, and every two columns are just Zero :) — Nestor C., Apr 29 '21 at 04:19

score 0 · Accepted Answer · answered Apr 28 '21 at 16:57

0

You could try using Evaluate.

Sub RoundToThree()
Dim rng As Range
Dim res As Variant

    Set rng = Range("E15")
    
    Set rng = Range(rng, rng.End(xlDown))
    
    Set rng = Range(rng, rng.End(xlToRight))
    
    res = Evaluate("ROUND(" & rng.Address & ",3)")
    
    rng.Value = res
    
End Sub

answered Apr 28 '21 at 16:57

norie

9,609
2
11
18

You are a Genius!, can you explain me the Evaluate purpose. Never worked with that :) – Nestor C. Apr 29 '21 at 04:24

Excel slow when rounding a huge range of cells (VBA)

2 Answers2