VBA code to remove numbers after 2 decimal places

Hi,

Use round(YourNumber, 2)

Regards

Sub test()

'to loose the digits after 2 decimals:
mVal = 344.873810587296
x = WorksheetFunction.RoundDown(mVal, 2)

'to round to the nearest digits after 2 decimals:
mVal = 344.875810587296
x = WorksheetFunction.Round(mVal, 2)

End Sub

Select allOpen in new window

Hi,

If it's a string, please use

CStr(Round(CDbl(YourTextNumber), 2))

Regards

I guess I should have been more specific, I have a whole column of numbers in column 'B' that I need to reformat.

Try this...

Sub NumberReformat()
Dim i As Long

For i = 1 To Sheet1.Range("B1").End(xlDown).Row
    If IsNumeric(Sheet1.Range("B" & i).Value) Then Sheet1.Range("B" & i).Value = Round(Sheet1.Range("B" & i).Value, 2)
Next i
End Sub


or simply this...

Sheet1.Columns("B:B").NumberFormat = "#,##0.00"

Keep in mind, that Round( ) does just that, some numbers will be rounded up, some down.

If you want to truncate, use:

[yourField]*100\100

or

Int([yourField]*100)/100

In an update query, that would look like:

Update yourTable SET [yourField] = INT([yourField]*100)/100