Speed up VBA rounding of cell values

Sam Hamels

New Member
Joined
Mar 20, 2018
Messages
49
The following code works perfectly to replace the values in a range of cells with the rounded versions.
As an example, I use a very small range [A1:C5] and rounding to 2 decimal points.


Code:
Sub Round_range()

Application.ScreenUpdating = False

    For Each cell In [A1:C5]
        cell.Value = WorksheetFunction.Round(cell.Value, 2)
    Next cell

Application.ScreenUpdating = True

End Sub

However, this code is rather slow when executed on a big range.
In my case, the range is [B4:AC8763], which is about 250.000 cells.
Running the above code takes about 25 seconds (!) on my modern laptop.

Is there any way I can achieve the same result, but significantly faster?
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Re: How to speed up VBA rounding of cell values

Get all data in one fell swoop, then round all of it and finally push it back again in one fell swoop:
Code:
Sub Round_range()
    Dim vData As Variant
    Dim lCol As Long
    Dim lRow As Long
    vData = Range("B4:AC8763").Value
    Application.ScreenUpdating = False
    For lRow = LBound(vData, 1) To UBound(vData, 1)
        For lCol = LBound(vData, 2) To UBound(vData, 2)
            vData(lRow, lCol) = WorksheetFunction.Round(vData(lRow, lCol), 2)
        Next
    Next
    Range("B4:AC8763").Value = vData
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Re: How to speed up VBA rounding of cell values

How about
Code:
Sub SamHamels()
   With Range("B4:AC8763")
     .Value = Evaluate(Replace("if(isnumber(@),round(@,2),@)", "@", .Address))
   End With
End Sub
But make sure you test it on a copy.
 
Upvote 0
Re: How to speed up VBA rounding of cell values

Interesting. I did some timing. My original code and yours:

JKP Fluff
0.87859583 0.3556337
(n=10)

Fluff's code much quicker

Then I edited my code to use the VBA Round function and got:

JKP Fluff
0.24187033 0.35378002
(n=10)

My code a bit quicker.

Unfortunately, the VBA ROund function rounds different from the Excel round function.
 
Upvote 0
Re: How to speed up VBA rounding of cell values

Unfortunately, the VBA ROund function rounds different from the Excel round function.
See below...



Get all data in one fell swoop, then round all of it and finally push it back again in one fell swoop:
Rich (BB code):
Sub Round_range()
    Dim vData As Variant
    Dim lCol As Long
    Dim lRow As Long
    vData = Range("B4:AC8763").Value
    Application.ScreenUpdating = False
    For lRow = LBound(vData, 1) To UBound(vData, 1)
        For lCol = LBound(vData, 2) To UBound(vData, 2)
            vData(lRow, lCol) = WorksheetFunction.Round(vData(lRow, lCol), 2)
        Next
    Next
    Range("B4:AC8763").Value = vData
    Application.ScreenUpdating = True
End Sub
Using WorksheetFunction.Round gets around the Banker's Rounding that VBA likes to use whenever it has to round any numbers; however, there is one native VBA function that uses normal rounding (the same kind Excel uses on its worksheet) and that function is the Format function. My thinking is that using built-in VBA function may be more efficient than calling out to the Excel engine for that calculation. Given that, the code line I highlighted in red above can be replace with this one...

vData(lRow, lCol) = CDbl(Format(vData(lRow, lCol), "0.00"))

You might not need the CDbl function call (I used it because Format returns a String value) as putting the numbers from the array into cells formatted as General may do that step for you (sorry, too lazy to test it),.
 
Last edited:
Upvote 0
Re: How to speed up VBA rounding of cell values

Thanks everybody!
I adapted JKP's code including Rick's suggestion and it works much faster indeed!


Cheers,
Sam
 
Upvote 0
Re: How to speed up VBA rounding of cell values

Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top