CODEWITHSUNDEEP
excelvbadecimal
Concerned_Citizen
Concerned_Citizen

Reputation: 6835

Comparing Decimal Values Excel VBA

I am trying to compare decimal values in Excel VBA and delete rows that match the criteria. This was my original code and it skipped over many rows.

For Each i In WSData.Range("A7", WSData.Range("A7").End(xlDown)).Cells
     If i.Offset(0, 3).Value >= 98 Then
        i.EntireRow.Delete
    End If
Next

And the values on the spreadsheet are decimal values just with the % sign.

enter image description here

I tried "> 97.99" because Excel has some issues with floating point comparison but it still doesn't accurately compare.

Here is what it shows after using Selection.Value. enter image description here

enter image description here

Upvotes: 0

Views: 2015

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

Percentages are decimal depicted with integers. For example 100.00% is stored as 1 and 98.01% is stored as .9801.

Therefor you need to adjust the threshold:

For Each i In WSData.Range("A7", WSData.Range("A7").End(xlDown)).Cells
     If i.Offset(0, 3).Value >= .98 Then
        i.EntireRow.Delete
     End If
Next

The second problem is that when deleting rows it is best to iterate backwards. Otherwise it might miss some rows, because as each row is deleted it is moved up and then the next iteration skips the next row.

Change i from a range to a long and use this:

For  i = WSData.Range("A7").End(xlDown).row to 7 Step -1
     If WSData.Cells(i,3).Value >= .98 Then
        Row(i).Delete
     End If
Next

Edit: Because it appears there is a custom mask on the number format that is forcing numbers to look like percentages try this:

For  i = WSData.Range("A7").End(xlDown).row to 7 Step -1
     If WSData.Cells(i,3).Value >= 98 Then
        Row(i).Delete
     End If
Next

If this works then your main problem was that you were looking at column D. The offset is additive. So when you used .offset(0,3) it was moving three columns from column A. 1 + 3 is 4.

Upvotes: 4

Related Questions