Rounding range of cells

[ACCA]

Does anyone know of a way to round a range of cells without doing it column
by column with the round function. For eg if all the numbers are in the
format 150.30 (to 2 dp) and we would like to round each to 150 (0 d.p). Can a
UDF etc be created to do this? If yes does anyone know how to do this? Thanks
in advance.

 

[Tim Williams]

I'm not sure how a UDF would be more convenient than just using a
macro

Sub tt()
Dim c As Range
For Each c In Selection.Cells
c.Value = Round(c.Value, 0)
Next c
End Sub

Tim

 

[Rick Rothstein]

Are we talking about constant values or values from formulas?

Also, you say your current values are formatted to 2 decimal places, but you
are looking to physically change the data, not just reformat it, correct?

 

[ACCA]

I'm not sure how a UDF would be more convenient than just using a
macro

Sub tt()
Dim c As Range
For Each c In Selection.Cells
c.Value = Round(c.Value, 0)
Next c
End Sub

Tim




.
Hi Tim/Rick,

Thanks guys for your prompt response.

Tim, I am an intermediate Excel user so I am not that exposed to VBA even
though I have bought some books and am reading. You are also right a UDF
would probably not be best. Can I just put the code you supplied in a vba
module and it will work or is it just a reference for me to try and create
the code.?

Rick, sorry if I did not explain the situation properly. I am looking for
something to change the cellls not just formatting. Hope this clarifies. In
the meantime I will also be reading my books as well to see if I can come up
with something.