Round a Range of Numbers in a Column

[Eric G]

Using VBA, how do I round a range of numbers in a column?

For example, I have a column of numbers in column A:

Row 1: 31.9
Row 2: 34.4
Row 3: 56.2
Row 4: 22.7

Where in column B they would appear rounded to the nearest whole number:

Row 1: 32
Row 2: 34
Row 3: 56
Row 4: 23

I imagine the answer involves a Looping statement and the Round function. If so or not, how would you solve what I am trying to accomplish here?

 

[Joe4]

No loops necessary. Assuming you want column B to be hard-coded values and not formula, try this:

Sub MyMacro()

    Dim lRow As Long
    
'   Find last row in column A
    lRow = Cells(Rows.Count, "A").End(xlUp).Row

'   Put rounding formula in column B, from row 1 to last row
    Range("B1:B" & lRow).FormulaR1C1 = "=ROUND(RC[-1],0)"
    
'   Convert formulas to hard-coded values
    Range("B1:B" & lRow).Value = Range("B1:B" & lRow).Value

End Sub

If you want the formula, just delete the last row of code before the "End Sub" statement.

 

[Rick Rothstein]

No loops necessary. Assuming you want column B to be hard-coded values and not formula, try this:

Sub MyMacro()

    Dim lRow As Long
    
'   Find last row in column A
    lRow = Cells(Rows.Count, "A").End(xlUp).Row

'   Put rounding formula in column B, from row 1 to last row
    Range("B1:B" & lRow).FormulaR1C1 = "=ROUND(RC[-1],0)"
    
'   Convert formulas to hard-coded values
    Range("B1:B" & lRow).Value = Range("B1:B" & lRow).Value

End Sub

If you want the formula, just delete the last row of code before the "End Sub" statement.

It may be a slight bit more efficient to put the calculated values directly into Column B rather than to put formulas into the cells only to replace them with their underlying values immediately afterward.

[table="width: 500"]
[tr]
	[td]Sub MyMacro()

    Dim lRow As Long
    
'   Find last row in Column A
    lRow = Cells(Rows.Count, "A").End(xlUp).Row

'   Put calculated rounded value into the cells of Column B
    Range("B1:B" & lRow) = Evaluate("IF(A1:A" & lRow & "="""","""",ROUND(A1:A" & lRow & ",0))")

End Sub[/td]
[/tr]
[/table]

 

[Joe4]

It may be a slight bit more efficient to put the calculated values directly into Column B rather than to put formulas into the cells only to replace them with their underlying values immediately afterward.

You piqued my curiosity Rick, so I ran a multiple tests on 100,000 rows of data.
Each method did it in exactly the same amount of time, 156 milliseconds.

So there doesn't really appear to be any difference in processing time.

 

[Eric G]

I get "#NAME?" for each rounded number using Rick's solution.
Is there something I am missing?

Also, is it possible to keep the rounded number in place from where it originated?

Originates in Column A:

Row 1: 31.9
Row 2: 34.4
Row 3: 56.2
Row 4: 22.7

Stays in Column A:

Row 1: 32
Row 2: 34
Row 3: 56
Row 4: 23

 

[Rick Rothstein]

You piqued my curiosity Rick, so I ran a multiple tests on 100,000 rows of data.
Each method did it in exactly the same amount of time, 156 milliseconds.

So there doesn't really appear to be any difference in processing time.

Hmm! That is very interesting... and not what I would have expected... I would surely thought forcing formulas into cells would have some overhead, especially if there were other formulas that I presume would get recalculated as the sheet updated... but it seems my guess at that is wrong. Thanks for running that test... it is good to know.

 

[Joe4]

Also, is it possible to keep the rounded number in place from where it originated?

One way, following the method I proposed:

Sub MyMacro()

    Dim lRow As Long
    
'   Find last row in column A
    lRow = Cells(Rows.Count, "A").End(xlUp).Row

'   Put rounding formula in column B, from row 1 to last row
    Range("B1:B" & lRow).FormulaR1C1 = "=ROUND(RC[-1],0)"
    
'   Convert formulas to hard-coded values in column A
    Range("A1:A" & lRow).Value = Range("B1:B" & lRow).Value

'   Clear column B
    Range("B1:B" & lRow).ClearContents
    
End Sub

 

[Rick Rothstein]

I am not sure why you should be getting a #NAME ! error with the code I posted as the only function names that could do that is the IF and ROUND which are spelled correctly... did you copy/paste what I posted (recommended) or simply try and type it out manually? Anyway, if you can figure out why it didn't work, the revision to my code is a little simpler as it does not require any help column in order to work...

[table="width: 500"]
[tr]
	[td]Sub MyMacro()

    Dim lRow As Long
    
'   Find last row in Column A
    lRow = Cells(Rows.Count, "A").End(xlUp).Row

'   Put calculated rounded values back into the cells of Column A
    Range("A1:A" & lRow) = Evaluate("IF(A1:A" & lRow & "="""","""",ROUND(A1:A" & lRow & ",0))")

End Sub[/td]
[/tr]
[/table]

 

[Eric G]

Found it! Geeez ... driving me nuts.

Instead of the "A1" reference style, I was using the "R1C1" reference style on the spreadsheet. It appears using the R1C1 reference style interferes with your coding, Rick, but not Joe's.

Because there is some other coding I am doing that it makes it easier for me to reference columns by numbers and not letters, and that I like the idea of bypassing the need for a helper column (just too cool to ignore), I am going see if I can modify your coding, Rick, to one the R1C1 reference style will accept.

Nonetheless, my thanks to the both of you for your solutions. It's nice to have options.

 

[Rick Rothstein]

Because there is some other coding I am doing that it makes it easier for me to reference columns by numbers and not letters, and that I like the idea of bypassing the need for a helper column (just too cool to ignore), I am going see if I can modify your coding, Rick, to one the R1C1 reference style will accept.

Give this modified version of my code a try...

[table="width: 500"]
[tr]
	[td]Sub MyMacroR1C1notation()

    Dim lRow As Long
    
'   Find last row in Column A
    lRow = Cells(Rows.Count, "A").End(xlUp).Row

'   Put calculated rounded values back into the cells of Column A
    Range("A1:A" & lRow) = Evaluate("IF(R1C1:R" & lRow & "C1="""","""",ROUND(R1C1:R" & lRow & "C1,0))")

End Sub[/td]
[/tr]
[/table]