How to Round Numbers in Every Excel Worksheet Using VBA
Question details
The user needs to round numeric constants across every worksheet within an Excel workbook and apply a consistent number format using a VBA macro.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Applying consistent decimal rounding and formatting to static numeric data distributed across multiple sheets without doing it manually.
- Observed behavior
- A programmatic solution is needed to loop through all worksheets, target the used ranges, round the actual values, and adjust the cell display formats simultaneously.
Before running a macro that alters cell values across multiple sheets, create a backup copy of your workbook, as VBA actions generally cannot be reversed using the standard Undo feature.
Use a VBA Macro to Round Numbers Across All Sheets
Run a VBA script to iterate through the UsedRange of every worksheet, rounding numeric constants and applying a uniform number format.
This method loops through every worksheet in the active workbook and targets cells containing hardcoded numbers (constants), ignoring text and formulas. It modifies both the underlying value and the display format.
Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications (VBA) Editor.
In the VBA Editor, click 'Insert' from the top menu and select 'Module' to create a new blank script window.
Set up a loop to iterate through all worksheets and select numeric constants: 'For Each ws In ActiveWorkbook.Worksheets' and then use 'SpecialCells(xlCellTypeConstants, xlNumbers)' to define the target cells.
Inside the cell loop, enter 'Num.Value = Round(Num.Value, 2)' to round the actual data to two decimal places, and 'Num.NumberFormat = "0.00"' to standardize the display.
Press F5 or click the Run button to execute the code. All numeric constants across your workbook will be rounded instantly.
Use Formulas or Ribbon Tools for Individual Sheets
If you prefer not to use macros, you can use built-in formulas or Ribbon buttons to round numbers on a sheet-by-sheet basis.
Automate Your Worksheets Easily with WPS Office
WPS Spreadsheets provides comprehensive support for VBA macros, allowing you to run automated scripts like rounding numbers across multiple worksheets smoothly and efficiently.
- 1. Download and Install: Download WPS Office for free from the official website and complete the installation.
- 2. Open Your Workbook: Launch WPS Spreadsheets and open your existing Excel workbook containing the data.
- 3. Access the Developer Tab: Navigate to the Developer tab to open the VBA Editor and easily insert or run your macro code.
Frequently Asked Questions
Why does my macro only change the visible formatting and not the actual value?
If you only apply 'Num.NumberFormat', only the visual display changes while the precise underlying number remains untouched. You must include 'Num.Value = Round(Num.Value, 2)' to permanently alter the underlying value.
Can I run this VBA script without affecting cells that contain formulas?
Yes. By targeting 'SpecialCells(xlCellTypeConstants, xlNumbers)' in your VBA code, the macro will exclusively round hardcoded numbers and safely bypass any cells that contain formulas.
How can I modify the macro to round to whole numbers?
To round to whole numbers, change the rounding argument to zero by using 'Round(Num.Value, 0)' and update the number format string to 'Num.NumberFormat = "0"'.
How do I apply this rounding macro to just one specific worksheet?
Instead of looping through 'Each ws In ActiveWorkbook.Worksheets', you can remove the worksheet loop and run the exact same logic targeting only 'ActiveSheet.UsedRange'.
