VBA & Macro Problems

How to Round Selected Excel Cells to Two Decimal Places with VBA

Ayan Masood Sep 28, 2026 871 views

Question details

The user needs a VBA method to round the values of currently selected Excel cells to exactly two decimal places in order to fix floating-point arithmetic errors.

How to Round Selected Excel Cells to Two Decimal Places with VBA

Product: Excel
Device & OS: not provided
Scenario: Calculating subtotals and totals with VBA occasionally leaves tiny fractional values, causing expected zero balances to display as positive or negative zero.
Observed behavior: Standard VBA calculations or formulas leave behind microscopic fractional anomalies instead of clean rounded numbers, which requires a post-calculation rounding fix.

Before you start

Before running the macro, ensure you have selected the specific cells you want to round and have converted any necessary formulas to static values, as VBA macros cannot be undone using the standard Undo button.

Solution 1Recommended

Use Application.Round in a VBA For Each Loop

Iterate through the selected cells using a VBA macro and apply the conventional Application.Round function to each value.

To properly round numbers conventionally (where 0.5 rounds up), you must use Excel's native rounding function via `Application.Round`. VBA's built-in `Round` function utilizes 'banker's rounding', which rounds to the nearest even number.

For best results when dealing with tiny fractional anomalies from subtotals, convert your formulas to values first, and then run this rounding script.

Open the VBA Editor

In your Excel workbook, press ALT + F11 to open the Microsoft Visual Basic for Applications window.

Insert a New Module

Click on Insert in the top menu and select Module to create a blank script window.

Input the Macro Code

Type the following code into the module: Sub RoundSelectedCells() Dim rngC As Range For Each rngC In Selection If IsNumeric(rngC.Value) And Not IsEmpty(rngC.Value) Then rngC.Value = Application.Round(rngC.Value, 2) End If Next rngC End Sub

Run the Macro on Selected Cells

Close the VBA editor. Highlight the cells containing the values you wish to round, press ALT + F8, select RoundSelectedCells, and click Run.

Use Application.Round in a VBA For Each Loop

Post-Calculation Fix: Rounding after converting formulas and subtotals into static values effectively eliminates floating-point errors and displays perfect zeros where expected.

Powerful VBA Support in WPS

Execute Macros and Format Data with WPS Spreadsheet

WPS Spreadsheet features robust built-in support for VBA macros, allowing you to run your cell-rounding scripts without any compatibility issues. It is a highly capable tool designed to streamline your data analysis workflows.

1. Open your Spreadsheet in WPS: Launch WPS Office, open the Spreadsheet module, and load your Excel workbook containing the data.
2. Access the Developer Tools: Navigate to the Developer tab on the ribbon and click on 'Macros', or press ALT + F8.
3. Run the Rounding Macro: Select the cells you want to modify, pick your rounding macro from the list, and click 'Run'.

Fully compatible with Microsoft Excel macro-enabled files (.xlsm) and standard workbooks (.xlsx).Includes a fully functional VBA editor to create, edit, and run scripts like Application.Round.Free, lightweight, and features a user-friendly tabbed interface for seamless task management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel create tiny fractional values during calculations?

This happens due to floating-point arithmetic. Computers store decimal numbers in binary code, which can cause microscopic precision errors during addition or subtraction, occasionally resulting in totals that are not perfectly zero.

What is the difference between VBA's Round function and Application.Round?

VBA's native Round function uses 'banker's rounding', which rounds exact 0.5 fractions to the nearest even number (e.g., 2.5 becomes 2). Application.Round calls Excel's standard rounding logic, which uses conventional rounding (e.g., 2.5 becomes 3).

Can I prevent these fractional values by rounding before the subtotals are calculated?

Rounding before calculations does not always resolve floating-point anomalies. The most effective practical solution is to let the subtotals calculate, convert them to static values, and then apply a rounding macro to the final results.

How do I prevent the VBA rounding macro from crashing on empty cells or text?

You can add validation within your For Each loop. Wrapping the rounding command in an IF statement like `If IsNumeric(rngC.Value) And Not IsEmpty(rngC.Value) Then` ensures the macro only processes cells that actually contain numbers.

Excel & Data Problems