How to Round a Range of Cells in Excel

This post will guide you how to round a range of cells with a formula in Excel. How do I round a range of cells using VBA macro in Excel 2013/2016. How to round up a range of numbers in a column in Excel.

Round a Range of Cells with ROUND Function


Assuming that you have a list of data in range B1:B5 which contain numbers with several decimal places. And you want to round them with only two decimal places. You can use a formula based on the ROUND function to achieve the result. Like as follows:

=ROUND(B1,2)

round range of cells1

Then you need to type the formula in a blank cell and drag the AutoFill handle over to other cells to apply this formula.

round range of cells2

ROUND a Range of Cells with VBA Macro


You can also use an Excel VBA Macro to achieve the same result of rounding a range of cells quickly in Excel. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

round range of cells3

Sub RoundNumber()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to round up", "RoundNumber", myRange.Address, Type:=8)
    dNum = Application.InputBox("Please type the number of decimal", "RoundNumber", Type:=1)
    For Each myCell In myRange
        myCell.Value = Application.WorksheetFunction.Round(myCell.Value, dNum)
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

round range of cells4

#6 Select one Range that you want to round up, such as: B1:B5

round range of cells5

#7 Please type the number of decimal that you want to round.

round range of cells6

#8 Let’s see the last result:

round range of cells7

Video: Round a Range of Cells

 

 

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar