Remove Decimals

[ineedsomevbhelp]

Greetings,

I have a macro which copies a column from worksheet A and pastes it in Worksheet B.

How can I create code so that when it pastes the values in Worksheet B it removes all the decimals.

I have changed the format-cells option to 0 decimal places. However, the value still reads as having decimals.

So for example, if I copy the number 95.67. It will show up as 95, but still use 95.67 for the calculations.

I want to get rid of the 0.67.....

Please help

Thanks
Raj

[jim mcnamara]

Use the Int() function in VBA

Dim j as Single, i as Integer
j=95.67
i = Int(j)
'              i now equals 95

[Hack]

If you convert your number to a Long, it will automatically drop the decimals. Example:

Dim MyNum As Integer
MyNum = 95.67
MyNum = CInt(MyNum)
'MyNum now a whole number

[jp_schwartz]

You have two options:

First one:
Change the Excel´s calculation settings, but... you will lost precision in every calculus of the sheet.

Second one (recomended):
Change your macro for copying, to make a cycle from the first row to the last row, so you can manipulate the values and sending the integer value.

If you need some... make a reply.

[Hack]

Drats...Jim beat me to it.

[ineedsomevbhelp]

Can you show me how to make it cycle through?....and change all the values in a range to read as zero decimals?

Thanks

Raj

[alex_read]

Range("A1:A10").Select
Selection.NumberFormat = "0"

Done !

[ineedsomevbhelp]

Still looking for help....need to change all the numbers in a column so that the decimals are permanently removed not just hidden.

Please Help
Raj

[alex_read]

Alright then :

Columns("A:A").Select
Selection.NumberFormat = "0"

if it's a column, this one will convert all the entries in column "A" as though you'd right clicked each, chosen the format cells option, selected the number category from the first tab & set the decimal points to 0. Can't see what's wrong with this & they are removed not hidden.

[alex_read]

Alternatively, if you want a long winded looping way, these too also work. Declare :

Dim intcounter As Integer, lastcol As Integer
lastcol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

Then use either :

For intcounter = 1 To lastcol
    Cells(1, intcounter).Select
    Round cell.Value, 0
Next intcounter

Or :

For intcounter = 1 To lastcol
    Cells(1, intcounter).Select
    cell.Value = CInt(cell.Value)
Next intcounter

All 3 methods work fine

[kovan]

i will put my 2 cents into this as well

mydouble = 3.4
 
dim pos as integer
pos = instr(1, mydouble, ".")
 
myNumberwithoutdec = mid(1, mydouble, pos -1)