How do I delete multiple cells (ie more than 5,000) which are not whole
numbers? There are many decimal numbers which are scattered across my
spreadsheet, and I would like them deleted. Thankyou.
How do I delete multiple cells (ie more than 5,000) which are not whole
numbers? There are many decimal numbers which are scattered across my
spreadsheet, and I would like them deleted. Thankyou.
Good morning Implicity
Highlight all the range you want checking for decimals and then try running this code:
Sub test()
occur = 0
On Error Resume Next
For Each rng In Selection
a = rng.Value
If Not a = Round(a, 0) Then
occur = occur + 1
If occur = 1 Then Set NotWhole = rng
If occur <> 1 Then Set NotWhole = Union(NotWhole, rng)
End If
Next rng
NotWhole.Select
End Sub
This should highlight all your decimal numbers and ignore any whole numbers. Once they're all selected just press delete. But BACK UP BEFORE you do this - I've tested the code, but just in case it selects something it shouldn't.
HTH
DominicB
Assuming your numbers start in cell A1. Put this formula =A1=TRUNC(A1,0)
into cell B2 and copy it all the way down. Integers in column A will have
TRUE in column B those cells with decimals will have FALSE nest to them.
Sort both columns by column B. Integers and non-integers are now grouped,
delete all the rows that have FALSE in column B.
Tom
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks