Delete Rows containing a certain textstring

Bukol

Board Regular
Joined
Mar 31, 2002
Messages
55
I am using Excel 2000.
I have a spreadsheet with 55000 e-mail addresses in column A. In order to remove duplicates I used the following macro code:
Sub delete_duplicates()
rowx = 1
Do Until Cells(rowx + 1, 1).Value = ""
If UCase(Cells(rowx, 1).Value) = UCase(Cells(rowx + 1, 1).Value) Then
Cells(rowx + 1, 1).EntireRow.Delete
Else
rowx = rowx + 1
End If
Loop
End Sub

Some of the addresses have an additional "REMOVED" added to the end of it, indicating the e-mail address is no longer valid.
How can I modify the above code to delete those rows as well.
Hans
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Try the following code:

If Right(Cells(rowx, 1).Value, 7) = "REMOVED" Then Cells(rowx + 1, 1).EntireRow.Delete
 
Upvote 0
Hans, thanks for the macro. I have been looking for exactly that process for a long time.

Rich
 
Upvote 0
Al Chara,
tried it out, had to add a ")" after the word REMOVED, but it gives me the message:
"Runtime Error 13 - type mismatch"
What now, shoot myself?
Hans
 
Upvote 0
The following code is what I am using and it works:

rowx = 1
If UCase(Right(Cells(rowx, 1).Value, 7)) = "REMOVED" Then
Cells(rowx, 1).EntireRow.Delete
End If
 
Upvote 0
Used your code exactly and it deleted every row except the first one wich contained REMOVED in it.
Dont worry, I had a backup.
Gonna shoot myself anyway.

Where is my mistake:
Sub delete_removed()
rowx = 1
Do Until Cells(rowx + 1, 1).Value = ""
If Right(Cells(rowx, 1).Value, 7 = "REMOVED") Then
Cells(rowx + 1, 1).EntireRow.Delete
Else
rowx = rowx + 1
End If
Loop
End Sub

Hans
 
Upvote 0
Sub delete_removed()
rowx = 1
Sorry posted the old code.
Here is the "Killer Code"

Do Until Cells(rowx + 1, 1).Value = ""
If UCase(Right(Cells(rowx, 1).Value, 7)) = "REMOVED" Then
Cells(rowx + 1, 1).EntireRow.Delete
Else
rowx = rowx + 1
End If
Loop
End Sub
 
Upvote 0
The following code worked for me:

rowx = 1
Do Until Cells(rowx + 1, 1).Value = ""
If UCase(Right(Cells(rowx, 1).Value, 7)) = "REMOVED" Then
Cells(rowx, 1).EntireRow.Delete
Else
rowx = rowx + 1
End If
Loop

I think you had "rowx+1" when you needed just "rowx"
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top