IsDate macro missing some blank cells

Jonathan King

New Member
Joined
Dec 13, 2018
Messages
17
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(y).Delete
End If
Next y

End With
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
A way (not conventional) to delete rows from top to bottom.

Code:
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:

Code:
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
 
Upvote 0
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!
 
Upvote 0
or maybe

Code:
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
 
Upvote 0
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.
 
Upvote 0
or maybe

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,816
Messages
6,181,143
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top