CODEWITHSUNDEEP
excelvbamodulo
Eric
Eric

Reputation: 23

How do I return the decimal portion of a number?

I have a data table that has values in column A consisting of integers and decimal numbers. (e.g. 1; 2; 3; 3.1; 3.2; 4... etc.).

I am trying to loop thru column A, find any values that are not integers and delete that entire row.

I am attempting to use the Mod function. I take the value of the cell in column A and when dividing that by 1, if the remainder is not zero delete the row.

My remainder is always zero, even when doing 10.1 Mod 1 for example.

Dim c As Range
Dim remainder As Variant
Dim rowNum As Integer

For Each c In Worksheets("BOM Dump").Range("A1:A1000").Cells
    
    'Range("L1").Value = "Number"
    'Range("M1").Value = c
   
    remainder = c.Value Mod 1
    Range("N2").Value = remainder
    
    If remainder <> 0 Then
        rowNum = c.Row
        Rows(rowNum).EntireRow.Delete
    End If
Next

Upvotes: 0

Views: 367

Answers (1)

Eric
Eric

Reputation: 23

Used fix instead of Mod and iterated backwards to get it working perfectly. Thanks everyone.

For i = 500 To 1 Step -1
        Set c = Worksheets("BOM Dump").Range("A" & i)
       
        remainder = c.Value - Fix(c.Value)
        
        If remainder <> 0 Then
            rowNum = c.Row
            Rows(rowNum).EntireRow.Delete
        End If
    Next

Upvotes: 1

Related Questions