check format

[Forestq]

Hi,

normalny in the column "M" I have data format (ex. 25.05.2013 12:10:55), but sometime the values are difrent: -628022.9725 or -623275.021412037

Now I want to remove all the values wrong values.

I have something like:

With sourceWb.Sheets(1)
                       LastRow = .Range("A65536").End(xlUp).Row

                                    For j = 1 To LastRow
                                        If .Range("M" & j).Value < 0 Then .Range("M" & j).ClearContents
                                    Next j
                   
End With

But I got error like: Run-time error '6': Overflow

 

[VoG]

Try

Dim LastRow As Long, j As Long
With sourceWb.Sheets(1)
    LastRow = .Range("A65536").End(xlUp).Row

        For j = 1 To LastRow
            If .Range("M" & j).Value < 0 Then .Range("M" & j).ClearContents
        Next j

End With

 

[Forestq]

I got but, the debug is showing me (highlight)

If .Range("M" & j).Value < 0 Then

 

[VoG]

I tested using this code with dates and large negative numbers in column M and it worked with no error

Sub test()
Dim LastRow As Long, j As Long
With ActiveSheet
    LastRow = .Range("M" & Rows.Count).End(xlUp).Row
        For j = 1 To LastRow
            If .Range("M" & j).Value < 0 Then .Range("M" & j).ClearContents
        Next j

End With

End Sub

 

[Forestq]

my first wrong value (-1000.323) is deleted, but on the second macro is stoped and I got error: Object required :/

I checked my data and the Format cells is : dd.mm.yyyy hh:mm:ss.

Maybe here is the problem???

 

[VoG]

Sorry but I cannot replicate that error.

 

[Forestq]

ok, thank you anyway!