delete row with specific content

[Galceran]

Excel 2003. This is a database of 892 entries. We already have a script which finishes by removing most of the columns ready for saving as a CSV file for an Address Book which is then emailed to the mailing dept. One member does not want the monthly updates. Fortunately his firstname is Andre the e is shown as acute.

At the start of formatting for the csv part we need to do a find for Andre and delete the row. This is the vba we are struggling with. As new members join Andre is likely to be moved down a row each time. The Alpha-Numeric sort takes place before the CSV formatting.

Thanks

 

[marka87uk]

The quickest method I've found for doing a similar exercise is to turn on the Autofilter, filter as appropriate and then delete the visible rows.

 

[Galceran]

Ah yes but, we want this in VBA, and it looks like it would always delete row 549, the next time Andre would be on a different row.
Thanks

 

[Galceran]

I did search before but now found this which seems to do the trick

Sub delandre()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("B" & i).Value = "Andrè" Then Rows(i).Delete
Next i
End Sub

Perhaps someone would confirm this is the best way. It is only Andre's row to be deleted.

Thanks

 

[Weaver]

Does the row with Andre only appear once? If so then this may be of use:

Sub test()
    Columns("A").Find(what:="Andrè", Lookat:=xlWhole).EntireRow.Delete
End Sub

 

[Galceran]

We get: Compile error. Duplicate declaration in current scope.
We already have Dim as LR. Changing LR to AR in the three instances appears to solve it.
Anyone to comment please?

 

[Galceran]

I hadn't seen your post Weaver when I posted. Yes, yours is the answer (only the one Andre).
As my bridge partner says. KISS (Keep it simple stupid!)

Thanks very much
Gilbert

 

[zookeepertx]

This is exactly the answer that I needed today! Thanks, Weaver!