PasteSpecial Values without Decimals

[hatstand]

Hi All,

I've been trying but failing to copy a range of values with random length decimal places and paste special, whilst rounding the values. Is it possible or does each value need to be done separately.

 

[doofusboy]

Why not paste and then format?

 

[Jonmo1]

I would put a formula in an adescent column that does the rounding.
Then copy/pastespecial that column.

=ROUND(A1,0)

 

[hatstand]

Thanks for the replies. The format works perfect. How the hell I didn't use it already. I put it down to being friday.

 

[Benjaminmin]

you could also just paste all the values, then highlight the range and set the format of the numbers to be with 0,1,2,X decimals?

 

[jim may]

I've been toying with your ideas now for an hour (for educational purposes).
Ideally, your activesheet doesn't have an event change macro in its code window.
If not, paste the below code into your sheets code window, and test it out....

Try this ONLY -- On a Backup copy of your file...

Let me know the outcome..

Jim

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim i As Long, RngCt As Long
On Error Resume Next
If Application.CutCopyMode = xlCopy Then
Application.EnableEvents = False
Set Rng = Target.Cells
RngCt = Rng.Cells.Count
    For i = 1 To RngCt
        Rng(i) = Int(Rng(i))
    Next i
End If
Application.EnableEvents = True
Application.CutCopyMode = False
On Error GoTo 0
End Sub