Delete rows with duplicate values
Hi Smohrman,
As coded, the macro tests all cells on each row to see whether thev're got
the same values as the corresponding cells on any other row.
From the data you'be posted, it's not clear where the column breaks might
be. If the data are all in one column, but parts of the data differ, you'll
need to use the Text-To Columns function to separate the different elements
into their own columns.
If you only want to test the cells in a limited number of columns, change
the line:
lLastCol = ActiveSheet.UsedRange.Columns.Count - 1
to 1 less than the column-number of the last column you want to test. For
example, if you want to test columns A-C, change this line to
lLastCol = 2
Similalrly, if you only want to start testing a column B, change the line:
For K = 0 To lLastCol
to
For K = 1 To lLastCol
Cheers
"Smohrman" wrote in message
...
OK, I resolved the syntax error- it no longer errors out. I run the
macro,
the hourglass turns and turns and turns...for about 2 minutes...then
nothing.
The column still has duplicate values in it. Is it time to punt or what?
"macropod" wrote:
Hi Smohrman,
For the most part, using the macro is as simple as:
.. opening up you Excel workbook at the worksheet you want to work on
.. pressing Alt-F11 to access the vba editor
.. clicking Insert|Module
.. copying & pasting the code I gave you into that module
.. either:
. pressing F5 to run the macro
. pressing Alt-F11 again to return to the worksheet and:
. pressing Alt-F8 to open the macro listing dialogue box
. selecting the macro and pressing 'Run'
Learning how to code them is somewhat more involved. {:-o}
Cheers
"Smohrman" wrote in message
...
Thanks macropod...man I wish I knew how to use that macro!
I've tried and tried but haven't had any success implementing macros.
No
one seems to have the time to explain it fully.
Nor (I should say) have I found the time to dedicate myself to
learning
how
to use macros...I know, I know- I'm going to have to do it
someday...after
I
write the novel inside me, take that trip to Hawaii, etc.
If you're not screaming busy and you could educate me with a series of
clear
steps on how to use the macro you've generated I'd certainly be
willing to
try again!
Thanks,
Smohrman
"macropod" wrote:
Hi Smohrman,
Here's a macro solution:
Sub DeleteDuplicateRows()
Dim lLastRow As Long
Dim lLastCol As Long
Dim I As Long
Dim J As Long
Dim K As Long
lLastRow = ActiveSheet.UsedRange.Rows.Count - 1
lLastCol = ActiveSheet.UsedRange.Columns.Count - 1
For I = 0 To lLastRow - 1
For J = lLastRow To I + 1 Step -1
For K = 0 To lLastCol
If ActiveSheet.Range("A1").Offset(I, K).Value <
ActiveSheet.Range("A1").Offset(J, K).Value Then
Exit For
End If
Next K
If K lLastCol Then
ActiveSheet.Range("A1").Offset(J, 0).EntireRow.Delete
End If
Next J
Next I
End Sub
Cheers
"Smohrman" wrote in message
...
Hi Team!
I have a spreadsheet with three colums of data. The first column
contains
records which have occasional phone number duplication- see
blelow:
(555) 000-0000 DataA1 ValueA1
(555) 000-0000 DataA2 ValueA2
(555) 555-9770 DataA3 ValueA3
(555) 555-4464 DataA4 ValueA4
(555) 555-4464 DataA5 ValueA5
(555) 555-4720 DataA6 ValueA6
(555) 555-8823 DataA7 ValueA7
(555) 555-3834 DataA8 ValueA8
(555) 555-4125 DataA9 ValueA9
What I need to do is (somehwhat) automate the process of filtering
or
deleting out all rows which have duplicate data in the first
column,
but
not
second or third columns. I'm sure it's been done...I tried the
Excel
out-of-the-box help suggestions and I've had no real luck. Any
ideas?
|