Automated deletion of decimals
 

Please help:

I need to delete all values that contain decimals from a worksheet, in one
fell swoop, if possible. For instance, the range I am currently working with
is Range("F7:IV2772").

I have whole numbers in many of the cells, and I only want those to remain.

Thank you so much, in advance, for your priceless help!!!


All the best,

Jeff

Automated deletion of decimals
 

Jeff,

The following code worked in very limited testing...
'------------------------
Sub TestNoDecimals()
'Jim Cone - San Francisco, USA - April 30, 2005
'Clears cells containing numbers that are not integers.
'Works only on the active worksheet in range F7:IV2772
'Note: formulas returning numeric values are included.

Dim rngNumbers As Excel.Range
Dim rngConstants As Excel.Range
Dim rngFormulas As Excel.Range
Dim rngCell As Excel.Range

On Error Resume Next
Set rngConstants = ActiveSheet.Range("F7:IV2772").SpecialCells(xlCell TypeConstants)
Set rngFormulas = ActiveSheet.Range("F7:IV2772").SpecialCells(xlCell TypeFormulas)
On Error GoTo 0

If rngConstants Is Nothing Then
Set rngConstants = rngFormulas
ElseIf rngFormulas Is Nothing Then
Set rngFormulas = rngConstants
End If
Set rngNumbers = Application.Union(rngConstants, rngFormulas)

For Each rngCell In rngNumbers
If IsNumeric(rngCell.Value2) Then
If (Int(rngCell.Value2) - rngCell.Value2) < 0 Then
rngCell.ClearContents
End If
End If
Next 'rngCell

Set rngNumbers = Nothing
Set rngConstants = Nothing
Set rngFormulas = Nothing
Set rngCell = Nothing
End Sub
'------------------------------


"Jeff" wrote in message
...
Please help:
I need to delete all values that contain decimals from a worksheet, in one
fell swoop, if possible. For instance, the range I am currently working with
is Range("F7:IV2772").
I have whole numbers in many of the cells, and I only want those to remain.
Thank you so much, in advance, for your priceless help!!!
All the best,
Jeff

Automated deletion of decimals
 

Jim,

Your post prompted me to look up Value2. I looked in Help and Googled it
and read that the "Value2 property doesn't use the Currency and Date data
types." I don't really understand what that means. Can you elucidate and
tell why you used it here?

Thanks,

Doug

"Jim Cone" wrote in message
...
Jeff,

The following code worked in very limited testing...
'------------------------
Sub TestNoDecimals()
'Jim Cone - San Francisco, USA - April 30, 2005
'Clears cells containing numbers that are not integers.
'Works only on the active worksheet in range F7:IV2772
'Note: formulas returning numeric values are included.

Dim rngNumbers As Excel.Range
Dim rngConstants As Excel.Range
Dim rngFormulas As Excel.Range
Dim rngCell As Excel.Range

On Error Resume Next
Set rngConstants =
ActiveSheet.Range("F7:IV2772").SpecialCells(xlCell TypeConstants)
Set rngFormulas =
ActiveSheet.Range("F7:IV2772").SpecialCells(xlCell TypeFormulas)
On Error GoTo 0

If rngConstants Is Nothing Then
Set rngConstants = rngFormulas
ElseIf rngFormulas Is Nothing Then
Set rngFormulas = rngConstants
End If
Set rngNumbers = Application.Union(rngConstants, rngFormulas)

For Each rngCell In rngNumbers
If IsNumeric(rngCell.Value2) Then
If (Int(rngCell.Value2) - rngCell.Value2) < 0 Then
rngCell.ClearContents
End If
End If
Next 'rngCell

Set rngNumbers = Nothing
Set rngConstants = Nothing
Set rngFormulas = Nothing
Set rngCell = Nothing
End Sub
'------------------------------


"Jeff" wrote in message
...
Please help:
I need to delete all values that contain decimals from a worksheet, in
one
fell swoop, if possible. For instance, the range I am currently working
with
is Range("F7:IV2772").
I have whole numbers in many of the cells, and I only want those to
remain.
Thank you so much, in advance, for your priceless help!!!
All the best,
Jeff

Automated deletion of decimals
 

Hi Doug,

It is a precaution I take when the datatype and number of decimals in the data are unknown.
Below are a couple of posts that explain it better than I can.
Running the example in the Chip Pearson post can make you a little afraid. <g

Regards,
Jim Cone
San Francisco, USA

'-------------------------------------------------
Chip Pearson May 18 2002, 11:53 am
Newsgroups: microsoft.public.excel.programming
Subject: Display Value Vs. Calculated Value...

Use the .Text property rather then the .Value property to retrieve exactly what
is displayed within a cell. The property returns a string data type, so you may
need to convert that to a numeric (e.g., CLng(Range("A1").Text) or
CDbl(Range("A1").Text) ) to get a real numeric data type from the string (text)
representation of that property.

On a related note, be aware that using the .Value property may give unexpected
results in cells formatted with the Currency style. For such cells, you may
want to use the .Value2 property to retrieve the internal value of the cell.
E.g.,

With Range("A1")
.Value = 12.3456789
.Style = "Currency"
Debug.Print "Text:", .Text
Debug.Print "CDbl(Text):", CDbl(.Text)
Debug.Print "Value:", .Value
Debug.Print "Value2:", .Value2
End With

Note the these results display are different. Value2 is always the most
accurate representation of what is truly in the cell.


'--------------------------------------------------------
Jim Rech Aug 9 2001, 4:40 am
Newsgroups: microsoft.public.excel.programming

Subject: Oddity
At the risk of beating this to death, Alan, it comes back to me that when VB
does math with variables of different data types it "promotes" one or both
of them to be the same and the result is then of that common data type.

typename(ccur(123.456)*cint(1)) = Currency (Curr * Int)
typename(ccur(123.456)*cdbl(1)) = Double (Curr * Dbl)
typename(ccur(123.456)*clng(1)) = Double (Curr * Long)
typename(ccur(123.456)*csng(1))= Double (Curr * Single) (Both promoted)

and

typename(ccur(123.456)*cint(1)/cint(1)) = Double
(looks as if when VB sees division or a decimal point it goes to Double)
So what's the lesson in all this? Know your data types?<g Always use
..Value2 unless you need the special handling .Value provides for Currency
and Date data types?
I'd sum it up like this: Be aware of the interaction of currency formatted
cells and .Value. When .Value reads a currency formatted cell it creates a
data item of type Currency in VB memory. This is inherently limited to 4
decimal places. And when .Value is used to put a Currency data type number
in a cell it rounds it to 2 decimal places in the process.
'-------------------------------------------------------------------


"Doug Glancy" wrote in message
...
Jim,
Your post prompted me to look up Value2. I looked in Help and Googled it
and read that the "Value2 property doesn't use the Currency and Date data
types." I don't really understand what that means. Can you elucidate and
tell why you used it here?
Thanks,
Doug


"Jim Cone" wrote in message
...
Jeff,
The following code worked in very limited testing...
'------------------------
Sub TestNoDecimals()
'Jim Cone - San Francisco, USA - April 30, 2005
'Clears cells containing numbers that are not integers.
'Works only on the active worksheet in range F7:IV2772
'Note: formulas returning numeric values are included.

Dim rngNumbers As Excel.Range
Dim rngConstants As Excel.Range
Dim rngFormulas As Excel.Range
Dim rngCell As Excel.Range

On Error Resume Next
Set rngConstants =
ActiveSheet.Range("F7:IV2772").SpecialCells(xlCell TypeConstants)
Set rngFormulas =
ActiveSheet.Range("F7:IV2772").SpecialCells(xlCell TypeFormulas)
On Error GoTo 0

If rngConstants Is Nothing Then
Set rngConstants = rngFormulas
ElseIf rngFormulas Is Nothing Then
Set rngFormulas = rngConstants
End If
Set rngNumbers = Application.Union(rngConstants, rngFormulas)

For Each rngCell In rngNumbers
If IsNumeric(rngCell.Value2) Then
If (Int(rngCell.Value2) - rngCell.Value2) < 0 Then
rngCell.ClearContents
End If
End If
Next 'rngCell

Set rngNumbers = Nothing
Set rngConstants = Nothing
Set rngFormulas = Nothing
Set rngCell = Nothing
End Sub
'------------------------------

Automated deletion of decimals
 

Thanks Jim,

That's interesting.

Doug

"Jim Cone" wrote in message
...
Hi Doug,

It is a precaution I take when the datatype and number of decimals in the
data are unknown.
Below are a couple of posts that explain it better than I can.
Running the example in the Chip Pearson post can make you a little afraid.
<g

Regards,
Jim Cone
San Francisco, USA

'-------------------------------------------------
Chip Pearson May 18 2002, 11:53 am
Newsgroups: microsoft.public.excel.programming
Subject: Display Value Vs. Calculated Value...

Use the .Text property rather then the .Value property to retrieve exactly
what
is displayed within a cell. The property returns a string data type, so
you may
need to convert that to a numeric (e.g., CLng(Range("A1").Text) or
CDbl(Range("A1").Text) ) to get a real numeric data type from the string
(text)
representation of that property.

On a related note, be aware that using the .Value property may give
unexpected
results in cells formatted with the Currency style. For such cells, you
may
want to use the .Value2 property to retrieve the internal value of the
cell.
E.g.,

With Range("A1")
.Value = 12.3456789
.Style = "Currency"
Debug.Print "Text:", .Text
Debug.Print "CDbl(Text):", CDbl(.Text)
Debug.Print "Value:", .Value
Debug.Print "Value2:", .Value2
End With

Note the these results display are different. Value2 is always the most
accurate representation of what is truly in the cell.


'--------------------------------------------------------
Jim Rech Aug 9 2001, 4:40 am
Newsgroups: microsoft.public.excel.programming

Subject: Oddity
At the risk of beating this to death, Alan, it comes back to me that when
VB
does math with variables of different data types it "promotes" one or both
of them to be the same and the result is then of that common data type.

typename(ccur(123.456)*cint(1)) = Currency (Curr * Int)
typename(ccur(123.456)*cdbl(1)) = Double (Curr * Dbl)
typename(ccur(123.456)*clng(1)) = Double (Curr * Long)
typename(ccur(123.456)*csng(1))= Double (Curr * Single) (Both promoted)

and

typename(ccur(123.456)*cint(1)/cint(1)) = Double
(looks as if when VB sees division or a decimal point it goes to Double)
So what's the lesson in all this? Know your data types?<g Always use
.Value2 unless you need the special handling .Value provides for Currency
and Date data types?
I'd sum it up like this: Be aware of the interaction of currency
formatted
cells and .Value. When .Value reads a currency formatted cell it creates
a
data item of type Currency in VB memory. This is inherently limited to 4
decimal places. And when .Value is used to put a Currency data type
number
in a cell it rounds it to 2 decimal places in the process.
'-------------------------------------------------------------------


"Doug Glancy" wrote in message
...
Jim,
Your post prompted me to look up Value2. I looked in Help and Googled it
and read that the "Value2 property doesn't use the Currency and Date data
types." I don't really understand what that means. Can you elucidate
and
tell why you used it here?
Thanks,
Doug


"Jim Cone" wrote in message
...
Jeff,
The following code worked in very limited testing...
'------------------------
Sub TestNoDecimals()
'Jim Cone - San Francisco, USA - April 30, 2005
'Clears cells containing numbers that are not integers.
'Works only on the active worksheet in range F7:IV2772
'Note: formulas returning numeric values are included.

Dim rngNumbers As Excel.Range
Dim rngConstants As Excel.Range
Dim rngFormulas As Excel.Range
Dim rngCell As Excel.Range

On Error Resume Next
Set rngConstants =
ActiveSheet.Range("F7:IV2772").SpecialCells(xlCell TypeConstants)
Set rngFormulas =
ActiveSheet.Range("F7:IV2772").SpecialCells(xlCell TypeFormulas)
On Error GoTo 0

If rngConstants Is Nothing Then
Set rngConstants = rngFormulas
ElseIf rngFormulas Is Nothing Then
Set rngFormulas = rngConstants
End If
Set rngNumbers = Application.Union(rngConstants, rngFormulas)

For Each rngCell In rngNumbers
If IsNumeric(rngCell.Value2) Then
If (Int(rngCell.Value2) - rngCell.Value2) < 0 Then
rngCell.ClearContents
End If
End If
Next 'rngCell

Set rngNumbers = Nothing
Set rngConstants = Nothing
Set rngFormulas = Nothing
Set rngCell = Nothing
End Sub
'------------------------------