Prev: Macro if criteria
Next: Floating text
From: B Lynn B on 7 Jun 2010 16:58
This assumes Column A is continuously populated from top to bottom of data
set. If not, then pick another column to use for finding last row. You can
also use the UsedRange property of the sheet if necessary.

Sub NoXDups()

Dim TestR As Long
Dim MyStr As String

For TestR = Range("A1").End(xlDown).Row To 1 Step -1
MyStr = Cells(TestR, "X").Value
If Range("X:X").Find(what:=MyStr, After:=Range("X1"), _
LookAt:=xlWhole).Row <> TestR Then
Rows(TestR & ":" & TestR).Delete shift:=xlUp
End If
Next TestR

End Sub


"Jbm" wrote:

> Hi,
> I checked out the archives for close to an hour, but I couldn't figure out
> how to change the codes given there to suit my needs.
> I have a large set of data, from about A1 to X441. In column X, there are a
> lot of exact duplicates, and I need to delete the rows where those duplicates
> are (but still leaving the first instance of the duplicate). For example:
>
> John Smith Oxford St.
> John Johnson Oxford St.
> John Johnson Rubble St.
> John Smith Oxford St.
>
> All of those have things in common, but I only want to delete the final row
> (and the whole row, not just the cell), because it is an exact duplicate.
> How do I code for this? Excel 2007.
> Thanks,
> Jbm
From: Jbm on 7 Jun 2010 17:19
I tried your macro, but it doesn't seem to be working... Maybe the fact that
there are headers is screwing it up? I've been working with your code since
you posted it, but I can't get it to work correctly (Column A has data in
every cell until the bottom of my data set).
From: Jbm on 7 Jun 2010 17:24
Ryguy -- I can't install new software on this machine.

Gord -- it's telling me that it removed 11 duplicates, and 400some unique
values remain. Despite this, all the duplicates I can see are still there
(and I am carefully checking to make sure they are the exact same.... They
are).
From: Don Guillett on 7 Jun 2010 17:34
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Jbm" <Jbm(a)discussions.microsoft.com> wrote in message
news:BAC9AF70-9077-42FE-B2AA-BCCEB45CC466(a)microsoft.com...
>I tried your macro, but it doesn't seem to be working... Maybe the fact
>that
> there are headers is screwing it up? I've been working with your code
> since
> you posted it, but I can't get it to work correctly (Column A has data in
> every cell until the bottom of my data set).

From: Jbm on 7 Jun 2010 17:34
Well this is deleting things, but not necessarily duplicates, and oftentimes
cells instead of rows (which means correlated data is getting thrown off).
Not all the data is in one cell, sorting would be allowed as long as the rows
of data each stay together.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Macro if criteria
Next: Floating text