Delete rows which are not formatted as dates

Question

In my column A I have cells which consists of formatted dates and general formatting. I want to delete rows which are not dates, and I've made this code, but I have to run it multiple times to get it to delete all the rows which aren't dates.

Code:

Sub del_row_not_date()

Dim rng_A_last As Long
Dim i As Integer

rng_A_last = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
Debug.Print rng_A_last

For i = 1 To rng_A_last Step 1
    If IsDate(Sheet1.Cells(i, 1)) = False Then
        Sheet1.Cells(i, 1).EntireRow.Delete
    End If
Next
End Sub

Thanks in advance!

user3598756 · Answer 1 · 2016-09-26T06:39:18.870

0

you could try

Sub del_row_not_date()
    With Sheet1
        .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
    End With
End Sub

what above deletes all column "A" not-numbers cells entire row

if you have cells with numbers that are not dates then use:

Option Explicit

Sub del_row_not_date()
    Dim i As Integer

    With Sheet1
        For i = .Cells(.Rows.Count, "A").End(xlUp).Row To 1 Step -1 '<--| loop backwards not to loose next row index after deleting the current one
            If Not IsDate(.Cells(i, 1)) Then .Cells(i, 1).EntireRow.Delete
        Next i
    End With
End Sub

edited Sep 26 '16 at 06:39

answered Sep 26 '16 at 06:31

user3598756

28,893
4
18
28

Thanks, I will try it later and comment on it! – Cobse Sep 26 '16 at 06:59
you are welcome. waiting for your comments to possibly further enhance answer. while code#2 actually solves your question, please mark answer as accepted. thank you – user3598756 Sep 26 '16 at 07:03

score 0 · Accepted Answer · edited May 23 '17 at 11:53

Since rows will

Based on this LINK I found this:

a tip about deleting rows based on a condition If you start at the top and work down, every time you delete a row your counter will effectively move to the cell two rows below the row you deleted because the row immediately below the deleted row moves up (i.e. it is not tested at all).

This works :)

Sub del_row_not_date()

Dim rng_A_last As Long
Dim i As Integer

rng_A_last = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
Debug.Print rng_A_last

For i = rng_A_last To 1 Step -1
    'Debug.Print Cells(i, 1).Value
    If IsDate(Sheet1.Cells(i, 1)) = False Then
        Sheet1.Cells(i, 1).EntireRow.Delete
    End If
Next
End Sub

That is precisely what I pointed out in my answer code #2. But if your dates are the only numbers in column "A" then my answer code#1 is much better (faster and shorter) — user3598756, Sep 26 '16 at 06:43

Delete rows which are not formatted as dates

2 Answers2