Macro to delete any rows in column ‘A’ which do not have a date in them

[robertguy]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Hi.

Can any please advise me of the macro code I need to check all cells in column ‘A’ for any cells that do not contain a date and then delete them N.B. the dates (to keep) will be in the format dd/mm/yy

Any help would be greatly appreciated

Many thanks in advance


Rob

N.B Excel version 2003 and I have asked this question in another forum i.e.
http://www.excelforum.com/excel-general/791108-macro-to-delete-any-rows-in-column-a-which-do-not-have-a-date-in-them.html which as yet has not been answered.

 

[VoG]

Try this - I assumed that you wanted the cells deleted not just blanked

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    With Range("A" & i)
        If Not IsDate(.Value) Then .Delete shift:=xlShiftUp
    End With
Next i
End Sub

 

[robertguy]

Vog,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
many thanks for your prompt reply it is very much appreciated...that said I think I messed up my criteria in that the macro should identify any cells in column 'A' that does not have a date in e.g. dd/mm/yy or is blank then delete the entire corresponding row ensuring the rows move up so that are continuous.<o></o>
<o> </o><o> </o>
Any thought on how to amend your macro code ??<o></o>
<o> </o> <o></o>
regards<o></o>
<o> </o>
<o> </o> <o></o>
Rob<o></o>

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    With Range("A" & i)
        If Not IsDate(.Value) Then .EntireRow.Delete
    End With
Next i
End Sub

 

[robertguy]

VoG,

Excellent - just what I wanted....


Many thanks again


Bob