Round Selection - VBA

[rilzniak]

Hey everyone,

I'm looking to round a selected range of cells but the code seems to be taking a really long time to run. I'm hoping someone can help me improve what I've written:

Selection.SpecialCells(xlCellTypeVisible).Select

    For Each cell In Selection
        cell.Value = WorksheetFunction.Round(cell.Value, 2)
        On Error Resume Next
    Next

I've seen other posts related to this before but they were always specific to a range. Is it possible to convert my selection to a non-contiguous range, and then run it on that range or would that run at the same speed? Or is there a better way to perform the task?

Thanks in advance.

 

[jproffer]

You can take out the "WorksheetFunction." Round works by itself in VBA (RoundUp/Down does not, however). That might speed it up.

Barring that, how many cells are you trying to work on?

 

[Joe4]

Also, it may help speed up your code if you suppress Calculations and Screen Updating until your loop is finished, i.e.

Place this before your loop:

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

and then this after:

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

 

[rilzniak]

You can take out the "WorksheetFunction." Round works by itself in VBA (RoundUp/Down does not, however). That might speed it up.

Didn't know that. Thanks, will do.

Barring that, how many cells are you trying to work on?

In this instance, ~45,000. It will typically be a smaller range though.

 

[rilzniak]

Also, it may help speed up your code if you suppress Calculations and Screen Updating until your loop is finished,

Thanks. I usually do that with my code but didn't in this instance, for no good reason.