Macro deleting row if duplicates found

[theiliad2000x]

Is there a macro whereby if I have a list, all in one column, but
different rows:

Column A:

Row 1: Alpha Beta Gamma
Row 2: Alpha Alpha Alpha
Row 3: Beta Gamma Gamma
Row 4: Gamma Gamma Alpha


If I wanted to delete all rows in MS Excel that have the word "Beta"
in it, I should be left with:


Alpha Alpha Alpha
Gamma Gamma Alpha


Thanks.

 

[Dave Peterson]

I would add a header row, then do data|filter|autofilter on that column.

Use the dropdown arrow
Contains
beta

And then delete the visible rows

Then remove the autofilter.

 

[theiliad2000x]

I would add a header row, then do data|filter|autofilter on that column.

Use the dropdown arrow
Contains
beta

And then delete the visible rows

Then remove the autofilter.












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

Thanks, that sounds like a good idea, especially for a short list.
But my lists are always more than a thousand rows, and, unless I'm
missing a step here, it would take a very long time to delete all
those rows manually, that's why I was trying to find an automated
system.

 

[Gord Dibben]

"Delete the visible rows".

To select visible rows hit F5>Special>Visible rows only

Edit>Delete will delete all at once.


Gord Dibben MS Excel MVP

 

[Tim Zych]

Sub DeleteBeta()
Dim rng As Range, cell As Range, rngToDelete As Range
Set rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each cell In rng.Cells
If UCase(cell.Value) Like "*BETA*" Then
If rngToDelete Is Nothing Then
Set rngToDelete = cell
Else
Set rngToDelete = Union(rngToDelete, cell)
End If
End If
Next
If Not rngToDelete Is Nothing Then
rngToDelete.EntireRow.Delete
End If
End Sub

 

[theiliad2000x]

Thank you very much everyone for helping me out, I appreciate the
useful advise, everything has worked out.