Table of Contents
Rounding up values in VBA is a simple process. All you need to do is use the Round function, which takes two arguments: the value you want to round up and the number of decimal places you want to round it to. You can also use the Ceiling or Floor functions to round up or down to the nearest integer or multiple of a given number. Examples of each function are provided below.
You can use the RoundUp method in VBA to round values up.
This function uses the following basic syntax:
Sub RoundUpValue()
Range("B1") = WorksheetFunction.RoundUp(Range("A1"), 0)
End Sub
This particular example will round up the value in cell A1 to the nearest whole number and display the result in cell B1.
Note that the second argument in the RoundUp method specifies the number of digits to round where:
- -3 rounds up to the nearest thousand
- -2 rounds up to the nearest hundred
- -1 rounds up to the nearest ten
- 0 rounds up to the nearest whole number
- 1 rounds up to the nearest tenth (one decimal place)
- 2 rounds up to the nearest hundredth (two decimal places)
- 3 rounds up to the nearest thousandth (three decimal places)
And so on.
The following examples show how to use the RoundUp method in practice.
Example 1: Round Up to Nearest Whole Number in VBA
We can create the following macro to round up the value in cell A1 to the nearest whole number and display the result in cell B1:
Sub RoundUpValue()
Range("B1") = WorksheetFunction.RoundUp(Range("A1"), 0)
End Sub
When we run this macro, we receive the following output:
Notice that the value 1,432.78 in cell A1 has been rounded up to the nearest whole number of 1,433 in cell B1.
Example 2: Round Up to Nearest Hundred in VBA
We can create the following macro to round up the value in cell A1 to the nearest hundred and display the result in cell B1:
Sub RoundUpValue()
Range("B1") = WorksheetFunction.RoundUp(Range("A1"), -2)
End Sub
Notice that the value 1,432.78 in cell A1 has been rounded up to the nearest hundred of 1,500 in cell B1.
Example 3: Round Up to Nearest Tenth in VBA
We can create the following macro to round up the value in cell A1 to the nearest tenth (i.e. one decimal place) and display the result in cell B1:
Sub RoundUpValue()
Range("B1") = WorksheetFunction.RoundUp(Range("A1"), 1)
End Sub
When we run this macro, we receive the following output:
Notice that the value 1,432.78 in cell A1 has been rounded up to the nearest tenth of 1,432.8 in cell B1.
Note: You can find the complete documentation for the VBA RoundUp method .