Locate and Delete

[dparteka]

A1:A1000 has various text information in each cell, there are several cells that start with [lastModified]= followed by a date and time which may or may not be different from cell to cell… one example would look like this [lastModified]=05/24/11 17:58:04.

I’m looking for a macro to locate each occurrence of [lastModified]= and delete the row… not just clear it but delete the entire row causing everything below it to move up.

 

[Somendra Misra]

Hi dparteka,

Can you try below code:

Sub deleteEntireRow()
Dim lr As Long

lr = Worksheets("Sheet1").Range("A1").End(xlDown).Row
Application.ScreenUpdating = False
For i = 1 To lr
    If InStr(1, Range("A" & i), "[lastModified]", vbTextCompare) Then
        Range("A" & i).Select
        Selection.EntireRow.Delete
    End If
Next i

End Sub

Regards,

 

[dparteka]

SM... pretty slick, works very good but there is one glitch. Lets say A4, A5, A6, A7, A8, A9, A20, A21 all have [lastModified] in them... the macro deletes A4, A6, A8, A20, so what’s happening is if two adjoining rows have [lastModified] in them it deletes the first and skips over the next.

 

[Debraj]

Hi SM..
its time for debug...

 

[SirJB7]

Hi, all!

No debug needed at all. When processing a range for deletion it's a must to do it in reverse order form last to first instead of normal order form first to last. That's to avoid skipping each other entry as when you're positioned in the i-th element and you delete it, the (i+1)th takes it place, so when you add 1 to i in the cycle For...Next loop or Do...Loop or whatsoever you get the (i+2)th in the original sequence, hence missing the slippy (i+1)th.

So change this:

For i = 1 To lr

by this:

For i = lr To 1 Step -1

Regards!

 

[Somendra Misra]

@SirJB7 ... Thanks for your support and a nice lesson.

@dparteka ...Hope above suggestion will solve your issue.

Regards,

 

[dparteka]

Prefect, works great... thanks to all

 

[SirJB7]

Hi, dparteka!
Glad you solved it. Credit to Somendra Misra. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!