Macro deleting row if duplicates found
 

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.

Macro deleting row if duplicates found
 

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.

wrote:

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

Macro deleting row if duplicates found
 

On May 31, 11:34*am, Dave Peterson wrote:
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.





wrote:

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- 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.

Macro deleting row if duplicates found
 

"Delete the visible rows".

To select visible rows hit F5SpecialVisible rows only

EditDelete will delete all at once.


Gord Dibben MS Excel MVP

On Sat, 31 May 2008 10:16:22 -0700 (PDT), wrote:

On May 31, 11:34*am, Dave Peterson wrote:
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.





wrote:

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- 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.

Macro deleting row if duplicates found
 

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


--
Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

wrote in message
...
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.

Macro deleting row if duplicates found
 

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