Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 '------------------------------ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 '------------------------------ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count digits before decimals and after decimals | Excel Worksheet Functions | |||
#Ref! after row deletion | Excel Discussion (Misc queries) | |||
name deletion | Excel Discussion (Misc queries) | |||
How do I override a deletion box? | Excel Programming | |||
Row Deletion | Excel Programming |