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
If you set up a loop that would range all the cells, you could set up a simple test.
Dim val As Long
val = Cells(x,y)
If val <> Int(val) then Cells(x,y).Clear
I'm just a beginner, so there may be a better way. Hope this helps.
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(xlCellTypeConstants)
Set rngFormulas = ActiveSheet.Range("F7:IV2772").SpecialCells(xlCellTypeFormulas)
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" <[email protected]> wrote in message
news:[email protected]...
> 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
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" <[email protected]> wrote in message
news:[email protected]...
> 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(xlCellTypeConstants)
> Set rngFormulas =
> ActiveSheet.Range("F7:IV2772").SpecialCells(xlCellTypeFormulas)
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
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: Re: 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: Re: 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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(xlCellTypeConstants)
> > Set rngFormulas =
> > ActiveSheet.Range("F7:IV2772").SpecialCells(xlCellTypeFormulas)
> > 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
> > '------------------------------
Thanks Jim,
That's interesting.
Doug
"Jim Cone" <[email protected]> wrote in message
news:%[email protected]...
> 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: Re: 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: Re: 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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(xlCellTypeConstants)
>> > Set rngFormulas =
>> > ActiveSheet.Range("F7:IV2772").SpecialCells(xlCellTypeFormulas)
>> > 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
>> > '------------------------------
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks