# Thread: Round Numbers in a Range

1. Issue: Range A1:Z365 contains numbers. Is there any formula or code that would round all of the numbers to zero decimal places without having to go into each cell individually?
Jeff

2. Select the range and then run this code:

Code:
Option Explicit
Sub RoundSelection()
Dim rCell As Range
For Each rCell In Selection
rCell.Value = Round(rCell.Value, 0)
Next
Set rCell = Nothing
End Sub

Steve

3. [quote name='sdckapr' post='775885' date='19-May-2009 10:19']Select the range and then run this code:

Code:
Option Explicit
Sub RoundSelection()
Dim rCell As Range
For Each rCell In Selection
rCell.Value = Round(rCell.Value, 0)
Next
Set rCell = Nothing
End Sub

Steve[/quote]

Thanks Steve, that works great! I know I've seen a post on here somewhere that would actually insert the argument ROUND(XX,YY) into a selected or highlighted range. Do you recall?

4. You can use something like:

rCell.Formula = "=Round(" & rCell.Value & ", 0)"

Steve

5. [quote name='sdckapr' post='775889' date='19-May-2009 10:39']You can use something like:

rCell.Formula = "=Round(" & rCell.Value & ", 0)"

Steve[/quote]

Yep-that was it!
Thanks again.

6. [quote name='jlkirk' post='775891' date='19-May-2009 08:40'][/quote]
A fancier version I use lets you select number of rounding digits:
[codebox]Sub MakeRound()
Dim rngCell As Range
Dim wsWksht As Worksheet
Dim strFormula As String, strDigits As String
Dim intCalcSet As Integer

Application.ScreenUpdating = False
intCalcSet = Application.Calculation
Application.Calculation = xlCalculationManual

strDigits = InputBox("How many digits? ")
For Each wsWksht In ActiveWindow.SelectedSheets
If Not wsWksht.ProtectContents = True Then
For Each rngCell In Selection
If rngCell.HasFormula And Left\$(rngCell.Formula, 6) <> "=ROUND" Then
strFormula = "=ROUND(" & Right\$(rngCell.Formula, Len(rngCell.Formula) - 1) _
& "," & strDigits & ")"
rngCell.Formula = strFormula
End If
Next
End If
Next wsWksht
Application.Calculate
Application.Calculation = intCalcSet
Application.ScreenUpdating = True
End Sub[/codebox]

7. [quote name='jlkirk' post='775891' date='19-May-2009 10:40']Yep-that was it!
Thanks again.[/quote]

Thanks John. Unfortunately, the code I entered doesn't work:

Sub MakeRound()
Dim rngCell As Range
Dim wsWksht As Worksheet
Dim strFormula As String, strDigits As String
Dim intCalcSet As Integer
Application.ScreenUpdating = False
intCalcSet = Application.Calculation
Application.Calculation = xlCalculationManual
strDigits = InputBox("How many digits? ")
For Each wsWksht In ActiveWindow.SelectedSheets
If Not wsWksht.ProtectContents = True Then
For Each rngCell In Selection
If rngCell.HasFormula And Left\$(rngCell.Formula, 6) <> "=ROUND" Then
strFormula = "=ROUND(" & Right\$(rngCell.Formula, Len(rngCell.Formula) - 1) _
& "," & strDigits & ")"
rngCell.Formula = strFormula
End If
Next
End If
Next wsWksht
Application.Calculate
Application.Calculation = intCalcSet
Application.ScreenUpdating = True
End Sub

Any ideas?
Thanks again.

8. [quote name='jlkirk' post='775922' date='19-May-2009 10:25']Unfortunately, the code I entered doesn't work. Any ideas?[/quote]
It worked for me from your listing. See if the attachment helps with the code.

9. [quote name='jlkirk' post='775880' date='19-May-2009 14:22']Issue: Range A1:Z365 contains numbers. Is there any formula or code that would round all of the numbers to zero decimal places without having to go into each cell individually?
Jeff[/quote]

Just a thought:

Perhaps you can use Tools->Options->Edit and then tick the checkbox for Fixed decimal places, set to zero.
No code and no formulas.
You can then untick the checkbox if required.

zeddy

10. [quote name='JohnBF' post='775926' date='19-May-2009 12:42']It worked for me from your listing. See if the attachment helps with the code.[/quote]

John,
Here is a test file. I still couldn't get it to run.
Jeff

11. I don't see any code in your workbook.

(This might be because I'm opening it using the file converter - I don't have Excel 2007)

12. [quote name='HansV' post='775950' date='19-May-2009 14:01']I don't see any code in your workbook.

(This might be because I'm opening it using the file converter - I don't have Excel 2007)[/quote]

Hans,
I just opened it up from the website and the code is located in the Personal.xls workbook.
Thanks

13. [quote name='jlkirk' post='775951' date='19-May-2009 21:05']Hans,
I just opened it up from the website and the code is located in the Personal.xls workbook.
Thanks[/quote]
Unfortunately, I don't have access to your Personal.dot at this particular moment, so I can't tell you why the code fails.

14. [quote name='HansV' post='775952' date='19-May-2009 14:06']Unfortunately, I don't have access to your Personal.dot at this particular moment, so I can't tell you why the code fails.[/quote]
Thanks. Interesting note: I just tried it on an 03 filr and it worked just fine!

15. The code only works on cells containing a formula. Your sample sheet doesn't contain formulas, only constant values.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•