Overflow error in Excel macro

[303factory]

Hi

I've written code to go through each row of a spreadsheet, and delete any rows where the date does not fall within a specific range.

Unfortunatly on my spreadsheet I occasionally get large numbers in the date column due to corruption. I was hoping the 'isdate' function would allow me to remove these but I keep getting an <Overflow> when trying to read out the value using Range.Value

Exact error on the isdate line: Range("M" & curRow).Value = <Overflow>

Why won't it allow me to read this value out? The actual value in this case is 1.58456325028529E+29.. surely not too big for VB to handle??

Code below:

 noRows = Worksheets(1).UsedRange.Rows.Count
    Dim curRow As Long
    Dim thisDate As Date
    curRow = 2 
    Do While curRow < noRows + 1
        'check valid date
        If IsDate(Range("M" & curRow).Value) Then
            
            thisDate = Range("M" & curRow).Value
            
            If thisDate >= dtFromDate And thisDate <= dtToDate Then
                inRange = True
            Else
                inRange = False
            End If
    
            If inRange = False Then
                Rows(curRow & ":" & curRow).Select
                Selection.ClearContents
            End If
        Else ' clear if not valid date
            Rows(curRow & ":" & curRow).Select
            Selection.ClearContents
        End If 
        curRow = curRow + 1
    Loop

Any help would be appreciated, ive tried researching the common causes of overflow and yeilded no results :/

303factory

[si_the_geek]

Thread moved to Office Development/VBA forum (note that the "VB Editor" in Office programs is actually VBA rather than VB)

The "overflow" error always means that the number is too big for the data type... and it is not surprising that it is occurring here, as that number is huge, certainly more than can be stored to a Date data type (which is part of what IsDate will check).

What I would recommend is trying to store the value to a Double instead (as it supports numbers that large), and/or see if that value is bigger than the largest Date allowed (I'm not sure if VBA has the same maximum as VB, but for VB it is #12/31/9999#).

eg:

  If CDbl(Range("M" & curRow).Value) < #12/31/9999# Then
       'valid date
  Else
       'not a valid date
  End If

..of course you could use a lower "maximum" date that makes more sense to you, as I doubt you have any valid data after the year 2099 or so!

[westconn1]

works ok for me (excel 2000)

?isdate(1.58456325028529E+29)
False

works too if i put into a cell and test the range of the cell

[303factory]

aaah I see, thanks! Got it working now