duplicate lines in an excel spreadsheet

[zico martin 84]

Im sure there must be a way of removing duplicate lines for a spreadsheet but cant work out how to do it. I thought importing it into access and setting a primary key would have solved the problem but it isnt.

 

help?

[peasepud 59]

Did you import to access into an existing table with a primary key already or just in and then added the primary key?

 

What you need to do is make the table first, allocate the primary key to the field and then import the data into it, this will discard any duplicates (giving you the message that x records were not added.

 

However the following VBA subroutine should work (Im not at work and dont have Excel so cant test it!)

 

 

Sub DeleteDups()

col = 2

lastValue=Cells(1,col).value

for x = 2 to 1000 (or number of rows in spreadsheet)

currValue= cells(x,col).value

if (currValue = lastValue) then

Selection.Rows(x).EntireRow.Delete

x = x - 1

End if

lastValue = currValue

next x

 

Do me a favour though and take a backup before running that as it could be flawed and delete it all!

 

If you're not used to VBA then goto Tools>Macro>Visual basic then add a module and paste that code in there. Then make sure youve got the right worksheet selected and in order before running it.

[zico martin 84]

Did you import to access into an existing table with a primary key already or just in and then added the primary key?

 

What you need to do is make the table first, allocate the primary key to the field and then import the data into it, this will discard any duplicates (giving you the message that x records were not added.

 

However the following VBA subroutine should work (Im not at work and dont have Excel so cant test it!)

 

 

Sub DeleteDups()

col = 2

lastValue=Cells(1,col).value

for x = 2 to 1000 (or number of rows in spreadsheet)

currValue= cells(x,col).value

if (currValue = lastValue) then

Selection.Rows(x).EntireRow.Delete

x = x - 1

End if

lastValue = currValue

next x

 

Do me a favour though and take a backup before running that as it could be flawed and delete it all!

 

If you're not used to VBA then goto Tools>Macro>Visual basic then add a module and paste that code in there. Then make sure youve got the right worksheet selected and in order before running it.

 

 

 

thanks but it aint working

 

using the access route it tells me 'an error occured trying to import file ....'

 

 

using the visual basic code I get 'Compile error: Expected End Sub' - so i guessed I had to add the line 'End Sub' to the end - but then when I run it, it seems the program is running in a loop and never finishes.

 

Can anyone who knows a little about VB give me a hand?

[ewerk 26282]

Try this one, it works for me, obviously edit it to suit.

 

Option Explicit

 

Sub DeleteDups()

 

Dim x As Long

Dim LastRow As Long

 

LastRow = Range("A65536").End(xlUp).Row

For x = LastRow To 1 Step -1

If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then

Range("A" & x).EntireRow.Delete

End If

Next x

 

End Sub

[zico martin 84]

Try this one, it works for me, obviously edit it to suit.

 

Option Explicit

 

Sub DeleteDups()

 

Dim x As Long

Dim LastRow As Long

 

LastRow = Range("A65536").End(xlUp).Row

For x = LastRow To 1 Step -1

If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then

Range("A" & x).EntireRow.Delete

End If

Next x

 

End Sub

 

 

thanks guys

 

Ive actually realised there's an even easier way though:

 

data - filter - advanced filter - copy to new location and click 'unique records only'

 

sheesh all the time wasted and it was right under my nose eh!