IsDate macro missing some blank cells

[Jonathan King]

For some reason, IsDate is missing some blank cells and I can't seem to resolve the problem.

The blank cells have been thoroughly checked with IsBlank and IsEmpty and show as blank cells.

What happens is, the blank cells are then subtracted from a date and consistently return a -43524.

IsEmpty(.Range("I" & y).Value) was added as a second check, but some blank cells are still being missed.

Most likely, I'm missing something right before my eyes.

Any ideas?

Thanks beforehand!

----

With Sheets("Test")
lrow2 = .Cells(.Rows.Count, "A").End(xlUp).Row


For y = 1 To lrow2
If Not IsDate(.Range("I" & y).Value) Then
.Rows.Delete
End If
Next y

End With

 

[Rick Rothstein]

When you delete rows, you must iterate your loop from the bottom up (otherwise the second row of two contiguous rows will be missed when it moves up to take the place of the row immediately above it when that got deleted), so change your For statement to this...

For y = lrow2 to 1 Step -1

 

[DanteAmor]

A way (not conventional) to delete rows from top to bottom.

Sub test()
    With Sheets("Test")
        lrow2 = .Cells(.Rows.Count, "[B][COLOR=#ff0000]I[/COLOR][/B]").End(xlUp).Row
        y = 1
        While y <= lrow2
            If Not IsDate(.Range("[B][COLOR=#ff0000]I[/COLOR][/B]" & y).Value) Then
                .Rows(y).Delete
                lrow2 = lrow2 - 1
            Else
                y = y + 1
            End If
        Wend
    End With
End Sub

Another detail: when you calculate the last row with data, you must do it with the evaluation column, in this case if you are going to verify the date in "I", then the column for the last cell with data must also be the "I"

---

But the fastest way to erase large amounts of rows is with the following method:

Sub test2()
    Dim lrow2 As Long, i As Long
    Dim r As Range
    Application.ScreenUpdating = False
    With Sheets("Test")
        lrow2 = .Cells(.Rows.Count, "I").End(xlUp).Row
        For y = 1 To lrow2
            If Not IsDate(.Range("I" & y).Value) Then
                If r Is Nothing Then
                    Set r = .Range("I" & y)
                Else
                    Set r = Union(r, .Range("I" & y))
                End If
            End If
        Next
    End With
    r.EntireRow.Delete
    Set r = Nothing
    Application.ScreenUpdating = True
End Sub

 

[Jonathan King]

Thanks Dante! I'll give that code a try.

 

[DanteAmor]

You're welcome and thanks for the feedback

 

[Jonathan King]

No worries, Dante! I didn't skip your point about using the evaluation column as the last row column. In many cases I use it, and even used ended-up using it with the code associated with the posting. But I thought it's better to use a column that you know is a required value to be filled for every row, so a true last row number can be used. i.e., In this test case, if the last five rows are blank, won't the last row var be calculated incorrectly and those rows wont be deleted? For example: If column A is required in every row and is in all 100 rows of a sheet, and column I can have blank cells, with rows 95-100 being blank, won't the last row var be 95 and those rows will be missed? Or am I missing something and it will be 100?

This is helping me a lot, since I do a lot of last row calculations.

Thanks!

 

[Michael M]

or maybe

Sub test()
Dim lr As Long
lr = Cells(Rows.Count, "I").End(xlUp).Row
 With Sheets("Test").Range("I1:I" & lr)
        .Replace "", "#N/A"
        .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
End Sub

 

[Jonathan King]

Thanks Michael!

Do the NA's end up getting deleted as well?

 

[DanteAmor]

No worries, Dante! I didn't skip your point about using the evaluation column as the last row column. In many cases I use it, and even used ended-up using it with the code associated with the posting. But I thought it's better to use a column that you know is a required value to be filled for every row, so a true last row number can be used. i.e., In this test case, if the last five rows are blank, won't the last row var be calculated incorrectly and those rows wont be deleted? For example: If column A is required in every row and is in all 100 rows of a sheet, and column I can have blank cells, with rows 95-100 being blank, won't the last row var be 95 and those rows will be missed? Or am I missing something and it will be 100?

This is helping me a lot, since I do a lot of last row calculations.

Thanks!

The important thing is to reach the last row, if column A is mandatory, then it is correct.

 

[DanteAmor]

or maybe

Sub test()
Dim lr As Long
lr = Cells(Rows.Count, "I").End(xlUp).Row
 With Sheets("Test").Range("I1:I" & lr)
        .Replace "", "#N/A"
        .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
End Sub

But he does not want to delete empty cells, he wants to delete the different date.

If Not IsDate(.Range("I" & y).Value) Then