Solved: Cell contains certain text, delete row?

[merlin]

Hello All!!

Can anyone help with this:


I am trying to delete rows that contain a certain text string. For example, if a cell contains an e-mail address from a certain source, a domain, I want to delete the entire row.

A B C
First Name, Last Name, Email address

ie If a cell in column C contains 'ABC.com' delete the row. now, the cell will obviously contain more than just ABC.com, as it will contain the complete e-mail address, for example Fred@ABC.com, so the instruction needs to see if the cell contains the term rather than equals the term.

Many thanks for help!

Merlin

[Bob Phillips]

[vba]

Public Function DeleteRows()
Dim mpLastRow As Long
Dim mpRange As Range
Dim i As Long

With ActiveSheet

mpLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.Columns("E").Insert
.Range("E1").Value = "Test"
.Range("E2").Resize(mpLastRow - 1).Formula = "=ISNUMBER(SEARCH(""ABC.com"",C2))"
Set mpRange = .Range("E1").Resize(mpLastRow)
mpRange.AutoFilter field:=1, Criteria1:=True
On Error Resume Next
Set mpRange = mpRange.Offset(1, 0).SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not mpRange Is Nothing Then mpRange.EntireRow.Delete

.Columns("E").Delete
End With
End Function
[/vba]

[merlin]

Thanks for that, but it isnt working

Just so I am clear - I have three columns, A, B and C. i have 13,005 rows. Some of the entries in column C have a certain domain name, call it ABC.com, within their cells (eg fred@abc.com or tsmith@abc.com. I want to find those cells and delete the entire row associated and move up the blank rows so there are no blanks, ideally as well.

I tried pasting the code into my excel macro and "run" but nothing happened.

Sorry, total newbie to this.

MANY THANKS!!

[Bob Phillips]

Maybe post your workbook?

[merlin]

Hello XLD

Thanks for your reply but I dont want to post the workbook as it contains thousands of email addresses which, of course, are sensitive

This is the format of the workbook though:

A B C
1 Fred Smith fred@dfsdfasf.com
2 Joe Wolf jwolf@madeupname.com
3 Freda Name fredaname@another.com
4 Matt Matthews matt@abc.com
5 Sue Smith girl@rtetetet.org
6 John Doe doe@abc.com

etc etc

I dont want any records for "abc.com" and therefore would want to the macro to remove rows 4 and 6, etc and move up the remaining rows.

Thats it.

Any ideas?

Thanks

[Bob Phillips]

Well I tested it with data like that and it worked, and I have just repeated it with that data and it deleted two rows.

[merlin]

Hello again

Where you have this formula and refer to C2,
doesnt this tell the macro just to look in cell C2?



Formula = "=ISNUMBER(SEARCH(""ABC.com"",C2))"

[merlin]

THANKS!

Got it to work. All is well.

Many thanks for yout time, it is much appreciated

Merlin

[Bob Phillips]

Tell us what the problem was, for the archives.