Solved

# Remove all charaters after 2 decimal places

Posted on 2011-02-22
1,166 Views
I need to remove all numbers after 2 decimal places rounding accordingly in a specific column in vba

It would turn 27.545 to 27. 55

I need what's typed in the cell to actually look like 27.55 also since I am importing this spreadsheet after and it won't take if it's 27.545
0
Question by:techpr0
• 4
• 2

LVL 33

Expert Comment

ID: 34956622
Something like this should do it:

ActiveSheet.Cells(2, 2).Value = Round(ActiveSheet.Cells(2, 2).Value, 2)
0

LVL 33

Expert Comment

ID: 34956633
For the entire column, you can use something like this.

jppinto
``````Dim wb As Workbook, ws As Worksheet
Dim cell As Range

Set wb = ActiveWorkbook
Set ws = ActiveSheet

For Each cell In ws.Range("B:B")
cell.Value=Round(cell.Value,2)
Next cell
``````
0

Author Comment

ID: 34956730
jppinto,
Your second solution worked, but how do i get it to skip row 1 ( the header row)?
0

LVL 33

Accepted Solution

jppinto earned 400 total points
ID: 34956793

jppinto
``````Sub teste()
Dim wb As Workbook, ws As Worksheet
Dim cell As Range
Dim lstrow As Long
lstrow = Cells(65536, "B").End(xlUp).Row

Set wb = ActiveWorkbook
Set ws = ActiveSheet

For Each cell In ws.Range("B1:B" & lstrow)
cell.Value=Round(cell.Value,2)
Next cell

End Sub
``````
0

LVL 33

Assisted Solution

jppinto earned 400 total points
ID: 34956801
Sorry...typo! Change this line:

For Each cell In ws.Range("B1:B" & lstrow)

for this:

For Each cell In ws.Range("B2:B" & lstrow)
0

LVL 45

Assisted Solution

patrickab earned 100 total points
ID: 34956818
Maybe as below...

``````Perhaps like this (to adapt jpp's code a little)

Dim wb As Workbook, ws As Worksheet
Dim cell As Range
Dim rng as Range

Set wb = ActiveWorkbook
Set ws = ActiveSheet
Set rng = ws.Range(ws.cells(2,"B"),ws.cells(ws.rows.count,"B").end(xlup))

For Each cell In rng
cell=Round(cell,2)
Next cell
``````
0

LVL 45

Expert Comment

ID: 34968570
techpr0 - Thanks for the points - Patrick
0

## Featured Post

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.