HELP delete duplicate rows.  
Author Message
numlocked





PostPosted: Mon Sep 29 04:48:22 CDT 2003 Top

Excel Programming >> HELP delete duplicate rows.

I have a spreadsheet with lots of duplicate rows of data.
I have run =IF(COUNTIF($A$2:A2,A2)=1,"-","Delete") to see
where duplicates appear and this is fine. I have 1,576
records of which I only need to keep 645.

What I would like to know is, how would I run a macro to
delete any rows automatically and move the the deleted
lines up so there are no blank lines between the remaining
information?

Any help is greatly appreciated.

Many thanks

Malcolm

Excel459  
 
 
wolf





PostPosted: Mon Sep 29 04:48:22 CDT 2003 Top

Excel Programming >> HELP delete duplicate rows. Hi Malcolm

this macro deletes rows, which have the same content in
column A. It is sorting the rows and then deleting the
doubles.
You may have to modify it for your needs.

Sub Sort_data_delete_double()

Worksheets("Sheet1").Range("A1").Sort _
key1:=Worksheets("Sheet1").Range("A1")
Set currentCell = Worksheets("Sheet1").Range("A1")
Do While Not IsEmpty(currentCell)
Set nextCell = currentCell.Offset(1, 0)
If nextCell.Value = currentCell.Value Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell
Loop
End Sub

Wolf


>-----Original Message-----
>I have a spreadsheet with lots of duplicate rows of
data.
>I have run =IF(COUNTIF($A$2:A2,A2)=1,"-","Delete") to see
>where duplicates appear and this is fine. I have 1,576
>records of which I only need to keep 645.
>
>What I would like to know is, how would I run a macro to
>delete any rows automatically and move the the deleted
>lines up so there are no blank lines between the
remaining
>information?
>
>Any help is greatly appreciated.
>
>Many thanks
>
>Malcolm
>.
>
 
 
Filip





PostPosted: Mon Sep 29 05:48:14 CDT 2003 Top

Excel Programming >> HELP delete duplicate rows. One more (non programic) help

If you would like to delete duplicated rows you can do following.
1)Select all data in your sheet where you want to delete duplicate rows
2) Data - Filter - Extended filter and check the Checkbox Without duplicite
records (maybe its called little bit differently, I have Czech version of
Excel, so I'm just translating it to EN ;-))

Then Copy and Paste the data to different Sheet

Good luck

:-))

Filip








> Hi Malcolm
>
> this macro deletes rows, which have the same content in
> column A. It is sorting the rows and then deleting the
> doubles.
> You may have to modify it for your needs.
>
> Sub Sort_data_delete_double()
>
> Worksheets("Sheet1").Range("A1").Sort _
> key1:=Worksheets("Sheet1").Range("A1")
> Set currentCell = Worksheets("Sheet1").Range("A1")
> Do While Not IsEmpty(currentCell)
> Set nextCell = currentCell.Offset(1, 0)
> If nextCell.Value = currentCell.Value Then
> currentCell.EntireRow.Delete
> End If
> Set currentCell = nextCell
> Loop
> End Sub
>
> Wolf
>
>
> >-----Original Message-----
> >I have a spreadsheet with lots of duplicate rows of
> data.
> >I have run =IF(COUNTIF($A$2:A2,A2)=1,"-","Delete") to see
> >where duplicates appear and this is fine. I have 1,576
> >records of which I only need to keep 645.
> >
> >What I would like to know is, how would I run a macro to
> >delete any rows automatically and move the the deleted
> >lines up so there are no blank lines between the
> remaining
> >information?
> >
> >Any help is greatly appreciated.
> >
> >Many thanks
> >
> >Malcolm
> >.
> >