another delete rows...decimals

[JEHalm]

another problem with deleting rows. Trying to delete rows that have a value <0.98 in cells column O. I've tried several data types to no avail. lost again.


Dim p As Double

For p = Range("o1" & Range("o" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1

If Cells(p, 15).Value = 0.9 Then Cells(p, 1).EntireRow.Delete Shift:=xlUp

Next p


The values in column "O" are derived from col L/col K. All of the results are between 0 and 1.25. I'm trying to delete all values < 0.98.


I discovered that I need to copy past values to the nue mbers in Column O and I have to trim the decimals down to 2. i.e., 0.98, 0.34, etc. But many of the numbers have a long string of decimals. For some reason I cannot trim the decimals down to two using the number format tool. If I directly type in numbers in column O with two decimals the code works. any thoughts?

 

[Hui]

If Cells(p, 15).Value <0.98 Then Cells(p, 1).EntireRow.Delete Shift:=xlUp

 

[JEHalm]

Hui, this function works great. A few questions though.


Column O has a formula (col J /col H). The results give me a decimal (i.e. 0.95). For some reasons some of the cells have value (after calculations) that have up to 10 decimal points (i.e., 0.802348756444. The cells with extra decimals places (more than 2) get skipped when I run the procedure. This make me think I have to format the numbers in col o. I have tried it using the format number tool but it doesn't cut the decimal places down to 2. Is there a way to format the numbers in column to #.##? I've tried it but wouldn't work...maybe the format I'm trying isn't valid?


Also, should the formula in the formula bar indicates col J/ col H. OK?


Lastly. do I need to copy and paste/values in the results...col O?


ActiveCell.FormulaR1C1 = "= RC[-2]/RC[-5]"

ActiveCell.Copy


'Range("F2").Select

'Selection.Copy

'Range("F3").Select

Range(Selection, Selection.End(xlDown)).Select


'Range("F3:F10").Select

'

ActiveSheet.Paste

Selection.End(xlDown).Select

ActiveCell.Delete

'ActiveCell.Offset(1, 0).Select

'Application.CutCopyMode = False


JH

 

[Hui]

From VBA you can access 3 values from a cell and they can all return slightly different values dependent on the value in the cell


Using your example above:


Cells(p, 15).Value

This is the numerical value that the cell holds


Cells(p, 15).Value2

Value2 also returns the numerical value that is in the cell but doesn't support Numerical or Date data types.

Value2 is also faster than Value


Cells(p, 15).Text

This is the text the cell is displaying

so if you have a formula which returns 3.14159265

but you have a Round(3.141592654, 2)

Cells(p, 15).Text will return 3.14


If you have a Date (eg: 5 Jan 2012) and a Custom Number format of say dd.mmm-yy

Cells(p, 15).Text will return 05.Jan-12

 

[JEHalm]

Sorry Hui, I'm at my witts end with this one. I know this is something small that I have overlooked. Is there any way I can send the file to you? Or I could copy and past the whole code??

 

[Luke M]

JEHalm,

If you click on "excel ninja" under Hui's name and scroll to bottom of page, you can find his email. Or, perhaps this will shed some light on the problem.

[pre]

Sub DeleteRows()
Dim LastRow As Integer
Dim p As Integer
Dim RangeLimit As Double
'Determine the last row
LastRow = Range("O65536").End(xlUp).Row
'Set the limit
RangeLimit = 0.98

For p = LastRow To 2 Step -1
'Use the format method to force cells value to be evaluated
'to only two decimal places (note that this would round the number,
'not truncate it)
If Format(Cells(p, 15).Value, "#.##") < RangeLimit Then
Cells(p, 15).EntireRow.Delete
End If
Next p
End Sub

[/pre]

 

[JEHalm]

ah, works like a charm. Thanks Luke. Thanks to Hui too.